Wednesday, January 25, 2012

Assign a date to DateTime in AX 2012

I was working with a client where we were seeing an issue with a data import and the table OMHierarchyRelationship's ValidFrom and ValidTo date fields were importing wrong for the Organizational hierarchies in AX2012.

We wanted to set the ValidFrom to the date of 1/1/2005 (not derived from an AX function) and the ValidTo as the maximum date.  The maximum date seemed to be set to the valid of 'Never' but that was actually a max date of 12/31/2154.  Really, this is stupid because the world will 'end' on December 21, 2012.   That is 142 wasted daysYEARS of real estate my Microsoft friends... 

There is often a 'developer' issue of remembering how to set dates not using an AX function.  Unless you do it all the time, its always a subject that takes a few minutes to remember how to do things with. Don't lie... You know its true...  If you're coming from AX 4.0, welcome to the UTC shock.

AX 4.0 always stored the data type 'Date' behind the scenes as a DateTime but just hacked off the time part on the AX end. There was a time EDT which was stored in seconds. Not too bad as long as you don't mind dividing by 60 all the time... There were some huge improvements in AX 2009 and 2012 wiht the UTCDateTime functionality. The biggest is the ability to see the date and time in AX as it is in SQL as well as be able to dynamically display timezone changes to the users based on where they are located.

Anyways... There can be issues with the code needed to do paragraph two above.  Here is how to do it:
omHierarchyRelationship.ValidFrom  = DateTimeUtil::newDateTime(1\1\2005, 0);
omHierarchyRelationship.ValidTo    = DateTimeUtil::maxValue();

The maxValue() function will put the dateTime as the max date of 2154.  The newDateTime() function will allow you to set the field for what you specify if you use MM\DD\YYYY.  Make sure the slashes are '\' and not '/'.  It MUST be 1\1\2005 and not be 1/1/2005; note the back vs forward slashes.  There do not need to be single colon's around the variable.

I hope this helps!! Please put a comment below if it doesn't.  I'll make sure that it does.

2 comments:

  1. I am afraid you made a terrible mistake regarding the world ending in December 21, 2012. Certainly, that is 142 wasted YEARS of real estate... ;-)

    ReplyDelete
  2. Great call, Moxi! Technically, it is 51,688 days of extra wasted real estate in addition to the mentioned above which is slightly more than my 2012 minimal mathematical tolerance... lol. Corrections made above. :-)

    ReplyDelete