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);


Working with Dates in X++

I was working on some date related functionality the other day during which I discovered some really useful date functions in X++. Some of you might be familiar with some of this already, however it is always good to have a blog post out there that lists them all for future reference. I have also added sample code for the usage of each function.

Initialization

A date variable can be initialized with a date constant. The date literal in X++ has a predefined format i.e. dd\MM\yyyy. Note that backslash is used instead of forward slash and unlike string constants neither single nor double quotes is used.

transDate = 01\01\2015;


today

Returns the date of the machine on which the code is run.

transDate = today();


systemDateGet

Returns the session date in AX. You can view/set the session date and time using File > Tools > Session date and time.

transDate = systemDateGet();


mkDate

Creates a date using the specified day, month and year components.

transDate = mkDate(31, 12, 2015);


Boundaries

Minimum value is January 1, 1900 also know as a null date. Maximum value is December 31, 2154.

info(strFmt("%1", dateNull()));
info(strFmt("%1", dateMax()));


Arithmetic

Plus and minus operators can be used to add or subtract days to a date. Minus operator used with date operands calculates the difference in days.

resultDate = transDate + 14;
info(strFmt("%1", resultDate));
resultDate = transDate - 21;
info(strFmt("%1", resultDate));
info(int2str(transDate - dateNull()));


Day related functions

dayName

Returns name of the specified day in a week. It takes a serial number representing day of week e.g. 1 (Monday), 7 (Sunday) and 8 (Monday).

info(dayName(2));


dayOfMth

Returns day component of the specified date.

info(int2str(dayOfMth(transDate)));


dayOfWk

Returns a serial number representing day of the week in which the specified date falls. You may use the output of this as an input to the dayName function.

info(int2str(dayOfWk(transDate)));


dayOfYr

Returns a serial number representing day of the year in which the specified date falls. e.g. 3 (Jan 3) and 32 (Feb 1).

info(int2str(dayOfYr(transDate)));


Week related functions

wkOfYr

Returns a serial number representing week of the year in which the specified date falls.

info(int2str(wkOfYr(transDate)));


dateStartWk

Returns a date of first day in the week in which the specified date falls.

resultDate = dateStartWk(transDate);
info(strFmt("%1", resultDate));


dateEndWk

Returns a date of last day in the week in which the specified date falls.

resultDate = dateEndWk(transDate);
info(strFmt("%1", resultDate));


Month related functions

mthName

Returns name of the specified month in a year. It takes a serial number representing month of year e.g. 1 (Jan), 9 (Sep) and 15 (Mar).

info(mthName(5));


mthOfYr

Returns month component of the specified date. You may use the output of this as an input to the mthName function.

info(int2str(mthOfYr(transDate)));


prevMth

Returns a date in the previous month that corresponds most closely to the specified date.

resultDate = prevMth(transDate);
info(strFmt("%1", resultDate));


nextMth

Returns a date in the following month that corresponds most closely to the specified date.

resultDate = nextMth(transDate);
info(strFmt("%1", resultDate));


dateStartMth

Returns date of the first day of the month in which the specified date falls.

resultDate = dateStartMth(transDate);
info(strFmt("%1", resultDate));


dateEndMth

Returns date of the last day of the month in which the specified date falls.

resultDate = dateEndMth(transDate);
info(strFmt("%1", resultDate));


dateMthFwd

Returns a date after adding the specified number of months to the date. You may also pass a negative value to subtract the months.

resultDate = dateMthFwd(transDate, 6);
info(strFmt("%1", resultDate));


Quarter related functions

date2Qtr

Returns a serial number representing quarter in which the specified date falls e.g. 1 (Jan-Mar).

dateString = int2str(date2Qtr(transDate));
info(dateString);


prevQtr

Returns a date in the previous quarter that corresponds most closely to the specified date.

resultDate = prevQtr(transDate);
info(strFmt("%1", resultDate));


nextQtr

Returns a date in the following quarter that corresponds most closely to the specified date.

resultDate = nextQtr(transDate);
info(strFmt("%1", resultDate));


dateStartQtr

Returns date of the first day of the quarter in which the specified date falls.

resultDate = dateStartQtr(transDate);
info(strFmt("%1", resultDate));


dateEndQtr

Returns date of the last day of the quarter in which the specified date falls.

resultDate = dateEndQtr(transDate);
info(strFmt("%1", resultDate));


Year related functions

year

Returns the year component of the specified date.

info(int2str(year(transDate)));


yearDiff

Calculates the difference in years between the two dates. First date should be greater than the second for a +ve difference.

info(int2str(yearDiff(transDate, dateNull())));


prevYr

Returns a date in the previous year that corresponds most closely to the specified date.

resultDate = prevYr(transDate);
info(strFmt("%1", resultDate));


nextYr

Returns a date in the following year that corresponds most closely to the specified date.

resultDate = nextYr(transDate);
info(strFmt("%1", resultDate));


dateStartYr

Returns date of the first day of the year in which the specified date falls.

resultDate = dateStartYr(transDate);
info(strFmt("%1", resultDate));


dateEndYr

Returns date of the last day of the year in which the specified date falls.

resultDate = dateEndYr(transDate);
info(strFmt("%1", resultDate));


Conversion functions

date2num

Converts a date to an integer that corresponds to the number of days since 1 January, 1900.

dateString = int2str(date2num(transDate));
info(dateString);


date2str

Converts a date to a string using the provided format specifiers.

dateString = date2str(transDate, 321, DateDay::Digits2, DateSeparator::Slash, DateMonth::Digits2, DateSeparator::Slash, DateYear::Digits4, DateFlags::None);
info(dateString);

Pass -1 for all format specifiers to get a date formatted as per the user’s regional settings.

dateString = date2str(transDate, -1, -1, -1, -1, -1, -1, -1);
info(dateString);


date2StrUsr

Converts a date in to a string as per the user’s regional settings.

dateString = date2StrUsr(transDate);
info(dateString);


date2StrXpp

Converts a date in to an X++ date literal. Use this when adding date ranges to query build data sources using code.

dateString = date2StrXpp(transDate);
info(dateString);


date2Julian

Converts a Gregorian date into Julian date with format yyyyddd. ddd is the number of days elapsed in the year yyyy.

dateString = date2Julian(transDate);
info(dateString);


str2Date

Converts a string to date.

resultDate = str2Date("2015.12.25", 321);
info(strFmt("%1", resultDate));


str2DateDMY

Converts a string in DMY format to a date.

resultDate = str2DateDMY("14-08-1947");
info(strFmt("%1", resultDate));


AxErd

Today I came across a really useful portal that everyone working on AX should be aware of. It is AxErd, a portal hosted by Microsoft, that contains very valuable information about the data models of every module in the software.

You can take a look at module ERDs centered on an important entity within that module e.g. VendTrans in the Accounts payable module. You can see a list of all tables in a module and also their Parent-Child relationships. All this information comes very handy while understanding AX data model and flow, designing customizations and ballpark estimation.