Working with utcDateTime in X++

The secret behind the capability of Dynamics AX to work flawlessly in different time zones is its utcDateTime data type. It combines date, TimeOfDay and time zone information into a single data type enabling the consultants to achieve date and time related requirements in a way that is more close to how we think about time in our daily lives. Like other data types in AX, utcDateTime can also be extended as required and used as the backing type of a database field.

Initialization

You can initialize a utcDateTime variable as follows.

transDateTime = utcDateTimeNull();
transDateTime = DateTimeUtil::utcNow();
transDateTime = DateTimeUtil::getSystemDateTime();

The utcNow method returns the current system time on the server without any time zone offset applied. Therefore the date/time returned by this method may not match the time you see on your machine.

The getSystemDateTime method returns the session date/time that could be set using the File > Tools > Session date and time dialog.

The newDateTime method instantiates date/time using the date, TimeOfDay and time zone parameters specified. The time zone offset when specified gets removed from the resulting date/time.

secondsElapsed = 14 * 60 * 60; // 02:00 PM
userTimeZone = DateTimeUtil::getUserPreferredTimeZone();
transDateTime = DateTimeUtil::newDateTime(today(), secondsElapsed);
transDateTime = DateTimeUtil::newDateTime(today(), secondsElapsed, userTimeZone);


Boundaries

The minimum value is 1900-01-01T00:00:00 and the maximum value is 2154-12-31T23:59:59. Note that the utcDateTimeNull function and the minvalue method return the same value.

transDateTime = DateTimeUtil::minvalue();
transDateTime = DateTimeUtil::maxvalue();


Date and time components

You can extract the date components (day, month, year) and time components (hour, minute, second) from a utcDateTime value as follows.

info(strFmt("%1", DateTimeUtil::date(transDateTime)));
info(int2str(DateTimeUtil::time(transDateTime)));
info(int2str(DateTimeUtil::day(transDateTime)));
info(int2str(DateTimeUtil::month(transDateTime)));
info(int2str(DateTimeUtil::year(transDateTime)));
info(int2str(DateTimeUtil::hour(transDateTime)));
info(int2str(DateTimeUtil::minute(transDateTime)));
info(int2str(DateTimeUtil::second(transDateTime)));


Manipulation of date and time components

You can add or subtract seconds, minutes, hours, days, months and years as follows.

transDateTime = DateTimeUtil::addSeconds(transDateTime, 60);
transDateTime = DateTimeUtil::addMinutes(transDateTime, 719);
transDateTime = DateTimeUtil::addHours(transDateTime, 36);
transDateTime = DateTimeUtil::addDays(transDateTime, 28);
transDateTime = DateTimeUtil::addMonths(transDateTime, 11);
transDateTime = DateTimeUtil::addYears(transDateTime, -1);
info(int642str(DateTimeUtil::getDifference(transDateTime, DateTimeUtil::minValue())));

The getDifference method returns the number of seconds between the two utcDateTime values specified.

Conversion

utcDateTime to str conversion can be done as follows.

dateTimeStr = DateTimeUtil::toStr(transDateTime);
dateTimeStr = DateTimeUtil::toFormattedStr(transDateTime, 231, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Short, DateSeparator::Hyphen, DateYear::Digits4, TimeSeparator::Colon, TimeSeparator::Colon, DateFlags::None);

The toStr method returns a string in the yyyy-mm-ddThh:mm:ss format, this is the format that X++ compiler recognizes.

The toFormattedStr method takes several parameters to control the formatting of date and time.

anytpe and str values can be converted to utcDateTime as follows.

transDateTime = DateTimeUtil::anyToDateTime(2015-07-03T23:45:30);
transDateTime = DateTimeUtil::parse("2015-07-04T00:00:00");


Calendar

User preferred calendar can be determined as follows.

calendar = DateTimeUtil::getUserPreferredCalendar();
info(enum2str(calendar));


Time zones

There are a several methods available in the DateTimeUtil class that can be used to work with time zones in AX. You can determine company time zone, user time zone and the client machine’s time zone.

entityTimeZone = DateTimeUtil::getCompanyTimeZone();
userTimeZone = DateTimeUtil::getUserPreferredTimeZone();
clientTimeZone = DateTimeUtil::getClientMachineTimeZone();
originTimeZone = DateTimeUtil::getOriginatingTimeZone(transDateTime);

The getOriginatingTimeZone method returns the time zone in which the specified UTC date time value was originated.

The getTimeZoneId method returns the standard time zone ID without mentioning the offset and city/country name e.g. PACIFIC STANDARD TIME.

info(DateTimeUtil::getTimeZoneId(entityTimeZone));

The getTimeZoneOffset method calculates the minute offset by subtracting time zone of the specified UTC date time (first parameter) from the specified time zone (second parameter).

info(int2str(DateTimeUtil::getTimeZoneOffset(DateTimeUtil::utcNow(), Timezone::GMTPLUS0500ISLAMABAD_KARACHI)));

The applyTimeZoneOffset method applies the specified time zone to the specified UTC date time value. This method is frequently used to apply user’s time zone to a UTC date time value previously stored.

transDateTime = DateTimeUtil::applyTimeZoneOffset(transDateTime, userTimeZone);


applyTimeZoneOffsetFilter

User preferred time zone offset can be applied to a filter as follows.

query = new Query();
dsRfqTable = query.addDataSource(tableNum(PurchRFQTable), 'Rfq');
dsRfqTable.addSelectionField(fieldNum(PurchRFQTable, RFQId));
dsRfqTable.addSelectionField(fieldNum(PurchRFQTable, VendAccount));
dsRfqLine = dsRfqTable.addDataSource(tableNum(PurchRFQLine), 'RfqLine');
dsRfqLine.addSelectionField(fieldNum(PurchRFQLine, ItemId));
dsRfqLine.addSelectionField(fieldNum(PurchRFQLine, ExpiryDateTime));
dsRfqLine.relations(true);
dsRfqLine.joinMode(JoinMode::OuterJoin);
filter = query.addQueryFilter(dsRfqLine, fieldStr(PurchRFQLine, ExpiryDateTime));
filter.value(queryValue(2015-07-15T00:30:00));
dateTimeStr = DateTimeUtil::applyTimeZoneOffsetFilter(filter);
filter.value(dateTimeStr);
info(dateTimeStr);


applyTimeZoneOffsetRange

User preferred time zone offset can be applied to a range as follows.

query = new Query();
dsRfqTable = query.addDataSource(tableNum(PurchRFQTable), 'Rfq');
dsRfqTable.addSelectionField(fieldNum(PurchRFQTable, RFQId));
dsRfqTable.addSelectionField(fieldNum(PurchRFQTable, VendAccount));
dsRfqLine = dsRfqTable.addDataSource(tableNum(PurchRFQLine), 'RfqLine');
dsRfqLine.addSelectionField(fieldNum(PurchRFQLine, ItemId));
dsRfqLine.addSelectionField(fieldNum(PurchRFQLine, ExpiryDateTime));
dsRfqLine.relations(true);
dsRfqLine.joinMode(JoinMode::OuterJoin);
range = dsRfqLine.addRange(fieldNum(PurchRFQLine, ExpiryDateTime));
range.value(queryValue(2015-07-31T01:30:00));
dateTimeStr = DateTimeUtil::applyTimeZoneOffsetRange(range);
range.value(dateTimeStr);
info(dateTimeStr);

If you need to query a table and filer the records such that only the records that were created/updated on a particular day are shown, you can use the datetobeginUtcDateTime and datetoendUtcDateTime methods. They take a date and time zone and returns the UTC date time when this date would begin/end.

transDateTime = datetobeginUtcDateTime(today(), userTimeZone);
transDateTime = datetoendUtcDateTime(today(), userTimeZone);


Querying an external SQL database using .NET Interop

In one of the Dynamics AX implementations I have worked on, there was a requirement to query an external SQL database in order to integrate with an information system. Although the preferred way of integration is web services, in my case there were no services exposed by that system which AX could consume. Therefore the only option I had was to query the database directly.

I used the .NET Framework classes to connect and query the SQL database. See the code below for an example of how you can use classes in the System.Data.SqlClient namespace. You’ll need to replace the Database_Name and Server_Name placeholders within the connection string to make it work. Note that depending on how your SQL security is configured, you might be required to provide user ID and password in the connection string.

static void ConnectSqlClient(Args _args)
{
str connectionString;
str query;
TableName tableName;

System.Exception exception;
System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlCommand command;
System.Data.SqlClient.SqlDataReader dataReader;

connectionString = “Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Database_Name;Data Source=Server_Name;”;

try
{
connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();

query = “SELECT NAME FROM SYS.TABLES ORDER BY NAME”;
command = new System.Data.SqlClient.SqlCommand(query, connection);

dataReader = command.ExecuteReader();

while (dataReader.Read())
{
tableName = dataReader.get_Item(“NAME”);
info(tableName);
}

dataReader.Close();
connection.Close();
}
catch (Exception::Error)
{
connection.Close();
}
catch (Exception::CLRError)
{
connection.Close();

exception = CLRInterop::getLastException();

if (exception != null)
{
info(exception.ToString());
}
}
}

Working with Time in X++

Working with time is fairly simple in X++. If you are only concerned with time on any calendar day e.g. 09:30 AM, you should be using the TimeOfDay data type. A variable of this type is an integer representing the number of seconds elapsed since midnight.

Initialization

Initialize the time with 09:30 AM as follows:

secondsElapsed = 9 * 60 * 60 + 30 * 60;

Initialize the variable with current time as follows:

secondsElapsed = timeNow();


Boundaries

Valid values are from 0 to 86399, where 0 represents 12:00:00 AM and 86399 represents 11:59:59 PM. Setting a value higher than 86399 is allowed by the compiler however it will not be correctly interpreted.

secondsElapsed = 0;
secondsElapsed = timeMax();


Conversion

A TimeOfDay value can be converted in to string in a variety of ways. Use time2str function to specify the time separator and time format parameters. Use time2strHM to get hours and minutes in 24-hour format. Similarly, use time2strHMS to get hours, minutes and seconds in the string.

info(time2str(secondsElapsed, TimeSeparator::Auto, TimeFormat::Auto));
info(time2StrHM(secondsElapsed));
info(time2StrHMLeadingZero(secondsElapsed));
info(time2StrHMS(secondsElapsed));

Use str2time to convert a string representation of time to a TimeOfDay value.

secondsElapsed = str2time("11:20:56 am");
info(int2str(secondsElapsed));


Calculating elapsed time

Use the timeConsumed function to calculate and display the elapsed time. It takes the Start Time and End Time as integer parameters, calculates the difference and returns a string describing the elapsed time. E.g. elapsed time from 09:30:00 AM to 01:00:15 PM can be determined as follows:

info(timeConsumed(34200, 46815));


Extracting time from a utcdatetime field

If you want to extract time from a utcdatetime field in the database, see an example below:

select firstOnly createdDateTime from sysUserLog
order by createdDateTime desc
where sysUserLog.UserId == '';
secondsElapsed = DateTimeUtil::time(sysUserLog.createdDateTime);