Date Calculations

INDEX : 

1. Date functions 

2. get the difference between two dates in dynamics ax just convert the dates into numbers and subtract one from the other. The result would be the amount of days between these dates.

3.Print today date .

4. Retrieve system date and time 

5. Validating 2 date fields in AX

6) How to calculate the total number of weeks, weekends or count of sundays in a given date interval
7. All Date functions in AX 2012
8. Get Working Days in AX
9. Get Join Date based on Time zone
10. Deep Dive for str2Date
11. Conversions between datetime to date and vice versa.

=================================================================================

Some Important date functions in AX

Dayname: Retrieves the name of the day of the week specified by a number.

wkofyr : Calculates the week of the year in which a date falls

mthofyr: Retrieves the number of the month in the year for the specified date.

mthname : Retrieves the name of the specified month

mkdate : Creates a date based on three integers, which indicate the day, month, and year, respectively.

dayofmth: Retrieves the day of the specified date.

dayofyr: Retrieves the day of the specified date.

year: Get year from date

dayofwk: Calculates the day of the week for a date.

prevmth:  Retrieves the date in the previous month that corresponds most closely to the specified date.

prevyr: Retrieves the date in the previous year that corresponds most closely to the specified date.

prevqtr: Retrieves the date in the previous quarter that corresponds most closely to the specified date.

Today: Retrieves the current date on the system.

1.Date Functions :

static void date_Functions(Args _args)
{
Transdate    d;
;

d = today();

info(strfmt("Date - %1",d));

//Gets the month for the given date...
info(strfmt("Month - %1",mthofYr(d)));

//Gets the month name from the given date...
info(strfmt("Month Name - %1",mthname(mthofYr(d))));

//Gets the day for the given date...
info(strfmt("Day - %1",dayOfMth(d)));

//Gets the day name from the given date...
info(strfmt("Day Name - %1",dayname(dayOfMth(d))));

//Gets the year for the given date...
info(strfmt("Year - %1",year(d)));

//Gets the current weekday number from the date...
info(strfmt("Weekday number - %1",dayOfwk(d)));

//Gets the day of the year from the given date...
info(strfmt("Day of year - %1",dayOfyr(d)));

//Gets the week of the year from the given date...
info(strfmt("Week of the year - %1",wkofyr(d)));
}

========================================================================

2. get the difference between two dates in dynamics ax just convert the dates into numbers and subtract one from the other. The result would be the amount of days between these dates.

a)  today() - mkdate(31,01,2011) ;
or
days = date2num(Date123) - date2num(Date234);
or
 TblName.FieldName= date2num(today()) - date2num(AnotherTblName.DatefeildName);

b)  To get the difference between two DateTime Values just use the DateTimeUtil Class. The result is the difference in seconds. So just divide these through #secondsPerDay and you'll get the days


#timeConstants

days = DateTimeutil::getDifference(DateTime123, DateTime234) / #secondsPerDay;

c)date2num(systemdateget()) - date2num(urtable.date);
d) 

static void DateDiff(Args _args)

{

    TransDate   d1,d2;

    int daysdiff;

    ;

    d1  = 31\12\2010;

    d2  = today();

    daysDiff = d2 - d1;

    info(strfmt("%1",daysDiff));

 MkDate():

Creates a date based on three integers, which indicate the day, month, and year, respectively.

date mkDate(int day, int month, int year)
Example

static void mkDateExample(Args _arg)
{
     date d;
      ;
 
    // Returns the date 01\01\2005.
     d = mkDate(1, 1, 2005);  
    print d;
    pause;
}

Date Difference : 

c = DateTimeUtil::getDifference(_CaseDetailBase.ClosedDateTime,

                                _CaseDetailBase.createdDateTime);

CaseTimeTmp.TotalTime = c/(24*60*60);

================================================================================
3. To print today's date :
 job:,

    date d;
    d = today();
    info(date2str(d,123,-1,-1,-1,-1,-1)); 
===============================================================================
4. Retrieving/Getting System Date and Time in Dynamics AX :
TableName.FieldName= DateTimeUtil::getSystemDateTime();
or
         StringEditName = DateTimeUtil::getSystemDateTime();
=================================================================================
5. Validating two date fields in AX :

1.Create two fields as UtcDateTimeEdit of table TableName
2.Name it InTime and OutTime.
3.In the init() of that datasource or form depends on requirements.

    TableName.InTime = DateTimeUtil::getSystemDateTime();
    TableName.OutTime = DateTimeUtil::getSystemDateTime();
//here making the system date and time to the that field.

3.Override the modified method by modified each of the fields.

public boolean modified()
{
    boolean ret;

    ret = super();

    if(SMAServiceOrderTable.InTime > SMAServiceOrderTable.OutTime)
    {
        warning("In time cannot be greater than Out time");
        SMAServiceOrderTable.InTime = DateTimeUtil::getSystemDateTime();
        SMAServiceOrderTable.OutTime = DateTimeUtil::getSystemDateTime();
    }

    return ret;
}
=======================================================================

6) How to Calculate the total number of weeks,weekends or count of  Sundays in a given date interval.. 

static void calcDays(Args _args)
{
    FromDate    fromDate    = today();
    ToDate      toDate      = mkdate(31,01,2011);
    WeekDays    weekDay = WeekDays::Sunday; // the day we search after
    int         days;
    int         cntSpecDay;
    int         leadIn;
    int         leadOut;
    ;
    days = fromDate - toDate; // days difference
    //Calculate the No.Of weeks in given Date Intervals
    leadIn = any2int(fromDate - toDate) /7;
    cntSpecDay = days / 7; // how many sundays in complete weeks
    if (dayOfWk(fromDate) <= weekDay)
    {
        cntSpecDay++; // the day we search is in lead in week
    }
    if (dayOfWk(toDate) >= weekDay)
    {
        cntSpecDay++; // the day we search is in lead out week
    }
info(strfmt("%1 is %2 times embedded between %3 and %4", weekDay,cntSpecDay, fromDate, toDate));
  print days;
  print leadIn;
  pause;
}

Out put:
======
786== Days
112== Weeks
Sunday is 113 times embedded between 3/27/2013 and 1/31/2011
===============================================================================

7.  All Date Functions in AX 2012

Dates :

Date d= prevYr(datefrom);

Date : dayOfMth(systemdateget())

month from date : mthOfYr(systemdateget())

Year from date : year(systemdateget())     

Start of  month : DateStartMth(today());

End of month : endmth(today()) 

//Difference of dates in Data, Month, year

               Day     = intvNo(_to, joindate, intvScale::Day);

                month   = intvNo(_to, joindate, intvScale::Month);

                years   = intvNo(_to, joindate, intvScale::Year);

//UTCdatetime to DATE formate

                utcDateTime                     validateDate;

                validateDate = DateTimeUtil::addDays(validateDate, 5);

//To compare date only from utcdatetime until

if ( DateTimeUtil::date(validateDate) ==  DateTimeUtil::date(HcmPositionWorkerAssignment.ValidTo)) 

//converting Date to UTC date time

DateTimeUtil::newDateTime(today(), 0, DateTimeUtil::getCompanyTimeZone()); 

//Converting datetime strning to UTCDate time

 dateTime = str2datetime(“24.12.2011 12:01:59″, 123);

 dateTime = str2datetime( “2011/02/25 23:04:59″ ,321 );

 dateTime = str2datetime( “Feb-2011-25 11:04:59 pm” ,231 );

 dateTime = str2datetime( “2 25 2011 11:04:59 pm” ,123 ); 

The sequence 123 describes the position of day (1), month (2), year (3).

 =======================================================================

8, Getting Working days in  AX 

Below is the sample code to get the working days based on the start date and end date.


to get it based on each legal entity wise in ax 2012, we need to setup the working days template in : YEU/Organization administration/Common/Calendars/working time templates and setup the same for each company in  YEU/Organization administration/Common/Calendars/Period templates


//Holiday list


select count(RecId) from leaveData
where leaveData.HolidayDate <= Mzk_LeaveHistory.StartDate
&& leaveData.HolidayDate >= Mzk_LeaveHistory.EndDate;
{
countrecid = any2int(leaveData.RecId);
}


//Working days
for(i=sdate; i<= edate; i++)
{
select count(RecId) from line
where line.dataAreaId == curext()
&& line.WeekDay == dayOfWk(i);



if(line.RecId)
{
P = P+1;
}
}

=====================================================================
9, Get Join Date based on Time Zone 

we recently came across one issues on getting date from database, in Ax 2012 the data time concept is introduced for the more active storing of data. when are try to get the join date for the employee through x++ code, we will get the date as per the central time zone stored in data base.

To get it based on the user preferred time zone use below sample code: 


JoinDateEmpl = DateTimeUtil::applyTimeZoneOffset( hcmEmployment::findByWorker(_worker).ValidFrom, DateTimeUtil::getUserPreferredTimeZone());






 
  
 



  


Hijri calendar conversion 

  


static str
  NormaltoHijriDate(date _transDate)

  {

  

  TransDate dt;

  int HijriDt;

  int HijriMonth;

  str Yr,finaldate;

  int findate;

  CalendarConverter CalendarConverter;

  str Hijridat,HijriMnth,HijriYr;

  ;

  

  dt = _transDate;

  CalendarConverter = new CalendarConverter(PreferredCalendar::Hijri);

  HijriDt = CalendarConverter.dayofmth(dt);

  HijriMonth = CalendarConverter.mthofyr(dt);

  Yr = CalendarConverter.yearStr(dt);

  

  Hijridat = int2str(HijriDt);

  HijriMnth = int2str(HijriMonth);

  HijriYr = subStr(Yr,1,5);// Sub string is taken as i want to return only two
  digits of year you write as subStr(Yr,3,5);

  //finaldate = HijriYr+" " +HijriMnth+" " +Hijridat;

  finaldate        =
  Hijridat+"-"+HijriMnth+"-"+HijriYr;

  return finaldate;

  }

  

  

  

  
  


Georgian date to hijri date conversion: 

  


Georgian date to hijri date
  conversion:

  




  


Before that you have to
  write the new method in global class and call the this method through global
  class 

  


static void Job15(Args _args)

  


{

  


    date
  d;

  


    str
  a,k;

  


    str
  b;

  


    d=today();

  


     

  


    /*a=
  date2str(d,321,DateDay::Digits2,

  


       
  DateSeparator::Hyphen, // separator1

  


       
  DateMonth::Digits2,

  


       
  DateSeparator::Hyphen, // separator2

  


       
  DateYear::Digits4);*/

  


b= Global::NormaltoHijriDate(d);

  


    print
  b;

  


    pause;

  


}

  




  




  


Main code which you have to
  write new method in global class as:

  


static str NormaltoHijriDate(date
  _transDate)

  


{

  




  


TransDate dt;

  


int HijriDt;

  


int HijriMonth;

  


str Yr,finaldate;

  


int findate;

  


CalendarConverter CalendarConverter;

  


str
  Hijridat,HijriMnth,HijriYr;

  


;

  




  


dt = _transDate;

  


CalendarConverter = new
  CalendarConverter(PreferredCalendar::Hijri);

  


HijriDt = CalendarConverter.dayofmth(dt);

  


HijriMonth = CalendarConverter.mthofyr(dt);

  


Yr = CalendarConverter.yearStr(dt);

  




  


Hijridat = int2str(HijriDt);

  


HijriMnth = int2str(HijriMonth);

  


HijriYr = subStr(Yr,1,5);// Sub string is taken as i want to return only two
  digits of year you write as subStr(Yr,3,5);

  


//finaldate =
  HijriYr+" " +HijriMnth+" " +Hijridat;

  


finaldate        =
  Hijridat+"/"+HijriMnth+"/"+HijriYr;

  


return finaldate;

  


}

  




  


Date2str function:

  


1) strFmt("%1",
  myDate)) 

  2) date2Str(dateType, 123, -1, -1, -1, -1, -1,
  -1) this will use the users 

  regional settings which can be different
  between users. 

  3) Most flexible way date2Str(dateType, 123,
  DateDay::Digits2, 

  DateSeparator::Slash, DateMonth::Digits2, DateSeparator::Slash, 

  DateYear::Digits4), change the enum values to
  your preferred format. Have a 

  look here:
  http://msdn.microsoft.com/en-us/library/aa857241.aspx.

  




  




  


Example:

  


void DateTStringConvert()

  


{

  


date today=today();

  


str TodayDate;

  


;

  


 TodayDate = date2str(
  today,321,DateDay::Digits2,DateSeparator::Slash,DateMonth::Digits2,DateSeparator::Slash,DateYear::Digits4);

  


info(TodayDate );

  


}

  


 Note :

  


 The function date2str
   take following parameters :

  


1- the date .

  


2- the date style dmy or
  ymd or....etc .

  


3- number of digits for day
  .

  


4- separator 

  


5- number of digits
  for month.

  


6- separator

  


7- number of digits
  for year.

  




  


now you can use the date as
  string

  




  


example:

  




  


static void
  date2StrExample(Args _arg)

  {

  date d = today();

  str s;

  ;

  s = date2Str(d, 123, 2, -1, 2, -1, 2);

  print "Today's date is " + s;

  pause;

  } 

  




  






To find  Difference b/w Two dates in Days, Months, Years  :

 date d = 08\05\1980;
  

   date d1 = 30\05\2013;
     

int diff;
     int permissionyr, permissionmth;
   

  ;
     

 diff = dayOfMth(d1) - dayOfMth(d);
  

   permissionyr = year(d1) - year(d);
   

 permissionmth = mthOfYr(d1) - mthOfYr(d);
     
  

   info(strFmt("%1",diff));
     info(strFmt("%1",permissionyr));
      info(strFmt("%1",permissionmth));



To calculate second  saturday :

static void Job27(Args _args)

 {
   

TransDate postingday=today();
  

  TransDate FirofMonth;
 

   int strt,end,dayofweek;
   

 date reportingon;
    date d = 29\06\2013;
  

 ;
 

   FirofMonth=DateStartMth(postingday);
   

 dayofweek =dayOfWk(FirofMonth);
  

  strt = 8-dayofweek;
    end =strt + 7;
  

  if( dayOfWk(d) ==  WeekDays::Friday && (dayOfMth(d)>= strt && dayOfMth(d)<= end))  

 // WeekDays::Friday && WeekOfMonth::Second) 

//( dayOfWk(d)>=strt && dayOfWk(d)<=end))
  

  {
      

  reportingon = d + 1;
 

   }

    else if(dayOfWk(d)== WeekDays::Friday)  

//dayName(5))
    {
        reportingon = (d + 3);
    } 

    info(strFmt("%1",reportingon)); 

   // info(strFmt("%1",TransDate));
  

//  info(strFmt("%1",FirstOfMth));
 

 //  info(strFmt("%1",dayOfWk(FirstOfMth)));
  

//  info(strFmt("%1",dayofweek));
  //

//  info(strFmt("%1",strt));

   //  info(strFmt("%1",end));
}



To print months  between dates given :

 static void MonthBetweenDates(Args _args) 

 {
     int startmonthnumber,EndMonthNumber;
   

  int StartYear,EndYear,i,y;
     int month,yr,date2;
   

  date startdate,enddate,nextDate;
     int totalmonth;
    

 ;
     

startdate = 06\05\2012;
  

   enddate   = 06\04\2013;
    

 startmonthnumber = mthofyr(startdate); 

     EndMonthNumber   = mthOfYr(enddate);
  

   StartYear        = year(startdate);
    

 EndYear          = year(enddate);
   

     while(startdate <= enddate)
    

 {
         

info(strFmt("%1",mthName(mthOfYr(startdate))));
         startdate = nextMth(startdate);
  

   }

 }



================================================================================
Calculate Date Displacements for 3 different period Units



static void DateDiffA(Args _args)

{

   date TransDate;

   TransDate Calcdate(date       startDate,

                      Periods    periodQty,

                      PeriodUnit periodUnit)

   {

      TransDateTime TransDateTime=DateTimeUtil::newDateTime(startDate,0);

      switch (periodUnit)

      {

         case PeriodUnit::Day:

            return any2date(DateTimeUtil::addDays(TransDateTime,periodQty));

         case PeriodUnit::Month:

            return any2date(DateTimeUtil::addMonths(TransDateTime,periodQty));

         case PeriodUnit::Year:

            return any2date(DateTimeUtil::addYears(TransDateTime,periodQty));

      }

      throw error(Error::wrongUseOfFunction(funcname()));

   }

   ;

   TransDate=CalcDate(today(),3,PeriodUnit::Month);

   info(strfmt('The calculated date is %1',date2str(TransDate,123,2,2,2,2,4)));

}

=============================

Get Date before few months :

static void Job26(Args _args)  

{  

   TransDate    i,now = today(); 
    

         

    i = DateTimeUtil::Date(DateTimeUtil::addMonths(DateTimeUtil::newDateTime(now, 0, DateTimeUtil::getCompanyTimeZone()),-6)); 
  

  
    info(strFmt("%1",i)); 

 } 







=============================================================================

AX X++ str2Date function













From time to time, a user will need to pull a date from a string. More often than not it is from an XML or from some other location. In order to actually use this date passed, it needs to be of data type 'date' so it can be written to fields, used in queries etc.  To do this, a user can use the 'str2Date' function.

The str2Date function takes two parameters:



datestring = The date in string format. It can be separated by either a dash ('-') or slash ('/')
sequence = A three number combo using 1, 2, and 3 where 1 = day, 2 = month, and 3 = year. (e.g. 231 would be MM-YYY-DD, and 321 would be YYYY-MM-YY)

If the sequence doesn't match the string or has an error, a '0' will result. Note that its very important to distinguish between day and month as an invalid month (>12) will result in a 0 and if both are <13, the date can translate totally wrong.











static void date2StringTest(Args _args)
{
    str dateStrYMD = '2013-01-08';
    str dateStrMYD = '01-2013-08';
    str dateStrMDY = '01-08-2013';
    str dateStrDMY = '08-01-2013';
    str dateStrYMD2y = '13-01-08'; // two digit year
    str dateStrYMD1m = '2013-1-08'; // 1 digit month
    str dateStrYMDinv = '2013-14-08'; // invalid month
    str dateStrYMDslsh = '2013/1/08'; // Slash instead of dash
    str zeroResultStr = 'INVALID and will be 0';
    int i;

    // Note that the second parameter to the str2Date field is 3 numbers which must be a
    // combination using 1, 2, and 3 where 1 = day, 2 = month, and 3 = year
    // For example 231 would be MM-YYYY-DD, and 321 would be YYYY-MM-YY

    // Year Month Day test
    info (strFmt("----Start YMD test for %1----", dateStrYMD));
    info (strFmt("1. YMD - str2Date('%1', 321) - result: %2", dateStrYMD, str2Date(dateStrYMD, 321)));
    info (strFmt("2. YMD - str2Date('%1', 231) - result: %2", dateStrYMD, str2Date(dateStrYMD, 231)));
    info (strFmt("3. YMD - str2Date('%1', 213) - result: %2", dateStrYMD, str2Date(dateStrYMD, 213)));
    info (strFmt("4. YMD - str2Date('%1', 123) - result: %2", dateStrYMD, str2Date(dateStrYMD, 123)));
    info ("");

    // Month Year Day test
    info (strFmt("----Start MYD test for %1----", dateStrMYD));
    info (strFmt("5. MYD - str2Date('%1', 321) - result: %2", dateStrMYD, str2Date(dateStrMYD, 321)));
    info (strFmt("6. MYD - str2Date('%1', 231) - result: %2", dateStrMYD, str2Date(dateStrMYD, 231)));
    info (strFmt("7. MYD - str2Date('%1', 213) - result: %2", dateStrMYD, str2Date(dateStrMYD, 213)));
    info (strFmt("8. MYD - str2Date('%1', 123) - result: %2", dateStrMYD, str2Date(dateStrMYD, 123)));
    info ("");

    // Month Day Year test
    info (strFmt("----Start MDY test for %1----", dateStrMDY));
    info (strFmt("9.  MDY - str2Date('%1', 321) - result: %2", dateStrMDY, str2Date(dateStrMDY, 321)));
    info (strFmt("10. MDY - str2Date('%1', 231) - result: %2", dateStrMDY, str2Date(dateStrMDY, 231)));
    info (strFmt("11. MDY - str2Date('%1', 213) - result: %2", dateStrMDY, str2Date(dateStrMDY, 213)));
    info (strFmt("12. MDY - str2Date('%1', 123) - result: %2", dateStrMDY, str2Date(dateStrMDY, 123)));
    info ("");

    // Day Month Year test
    info (strFmt("----Start DMY test for %1----", dateStrDMY));
    info (strFmt("13. DMY - str2Date('%1', 321) - result: %2", dateStrDMY, str2Date(dateStrDMY, 321)));
    info (strFmt("14. DMY - str2Date('%1', 231) - result: %2", dateStrDMY, str2Date(dateStrDMY, 231)));
    info (strFmt("15. DMY - str2Date('%1', 213) - result: %2", dateStrDMY, str2Date(dateStrDMY, 213)));
    info (strFmt("16. DMY - str2Date('%1', 123) - result: %2", dateStrDMY, str2Date(dateStrDMY, 123)));
    info ("");

    // Other scenarios
    info ("----START OTHER SCENARIOS----");
    info (strFmt("17. YMD using %1 which only has 2 digits for the year (e.g. 13 instead of 2013) -            result: %2", dateStrYMD2y, str2Date(dateStrYMD2y, 321))); // 2 digit year
    info (strFmt("18. YMD using %1 which only has 1 digit for the month (e.g. 1 instead of 01) -               result: %2", dateStrYMD1m, str2Date(dateStrYMD1m, 321))); // 1 digit month
    info (strFmt("19. YMD using %1 which has an invalid numbe for the month (e.g. 14 is greater than 12) - result: %2", dateStrYMDInv, str2Date(dateStrYMDInv, 321))); // invalid month
    info (strFmt("20. YMD using %1 which has a slash instead of a dash (e.g. 2013/01/08 instead of 2013-01-08) - result: %2", dateStrYMDslsh, str2Date(dateStrYMDslsh, 321))); // different divider
}

11. Date time 2 Date and Vice Versa:

static void Datetime2dateconversion(Args _args)
{
    utcDateTime datetime;
    date   chkdate;
    datetime    =   DateTimeUtil::getSystemDateTime();
    info(strFmt("%1",datetime));
    chkdate =   DateTimeUtil::date(datetime);
    info(strFmt("%1",chkdate));
    datetime    =   datetoendUtcDateTime(chkdate,DateTimeUtil::getUserPreferredTimeZone());
     info(strFmt("%1",datetime));
}