Use of DateTime in select statement

April 26, 2009 No comment

Created/ModifiedDateTime is a datetime field and the value will be stored as UTCDateTime. So when you want to find all the records created/modified on a certain date, you have to find all the records between the start of the day and the end of the day.

The newDateTime() function is defined in the DateTimeUtil class and it is a utility function that creates a new UTCDateTime object. With addDays() you can add a certain amount of days.

Example:
All the SalesLine records created today.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
static void test_UTCDateTime(Args _args)
{
    SalesLine       salesLine;
    ;
    info(strfmt("%1", DateTimeUtil::newDateTime(systemdateget(), 0)));
    info(strfmt("%1", DateTimeUtil::addDays(DateTimeUtil::newDateTime(systemdateget(), 0), 1)));
 
    while   select salesLine
    where   salesLine.createdDateTime >= DateTimeUtil::newDateTime(systemdateget(), 0)
    &&      salesLine.createdDateTime < DateTimeUtil::addDays(DateTimeUtil::newDateTime(systemdateget(), 0), 1)
    {
        // do something
    }
}