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

Link – https://msdax.wordpress.com/2011/04/26/how-to-find-those-record-that-are-modified-on-certain-date-from-a-certain-table/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s