How to find those record that are modified on certain date from a certain table.

When i started working this, i thought it would be task like a click, because i was making logic in my mind to compare the CreatedDateTime field with today’s value (CreatedDateTime >= today()) but when i wrote this i got into trouble, as the compiler is not allowing me and give me error. The error suggest me that i need to convert the date to dateTime then i can use this in select to get my desired result. Not only i need to convert the date to dateTime but i also needs the gets min and max dateTime of todays date.

I open the most important GLOBAL class in AX and luckily i found methods there

datetobeginUtcDateTime(_currDate, _tz),
msdn description of the method: Retrieves a utcdatetime value that represents the beginning of the specified date.

_currDate :The date to retrieve the beginning of.
_tz : The time zone to use to find the start of the date
datetoendUtcDateTime(_currDate, _tz),
msdn description of the method:
Retrieves a utcdatetime value that represents the end of the specified date.

_currDate :The date to retrieve the end of.
_tz : The time zone to use to find the end of the date
After finding the above method to get the min and max datetime for the todays date, i can use them in the select to get userLog record created today.

select * from log
where log.CreatedDateTime >= datetobeginUtcDateTime(today(), DateTimeUtil::getUserPreferredTimeZone()) &&
log.CreatedDateTime <= datetoendUtcDateTime(today(), DateTimeUtil::getUserPreferredTimeZone());

Advertisements
%d bloggers like this: