CRM DATE TIME ISSUE

Link - http://blogs.infinite-x.net/2009/08/17/using-crm-4-0s-built-in-function-part-1-of-5/

dbo.fn_UTCToLocalTime

Converts a UTC datetime value into a local date and time value. Using modifications of our examples above:

select dbo.fn_UTCToLocalTime(dbo.fn_BeginOfHour(GetUTCDate()))

select dbo.fn_UTCToLocalTime(dbo.fn_BeginOfLastXHour(GetUTCDate(), 1))

select dbo.fn_UTCToLocalTime(dbo.fn_EndOfNextXHour(GetUTCDate(), 1))

Returns:

Beginning of Hour: 2009-08-16 12:00:00.000

Beginning of Last X Hour: 2009-08-16 11:00:00.000

Beginning of Next X Hour: 2009-08-16 14:00:00.000

dbo.fn_LocalTimeToUTC

Just the opposite of dbo.fn_UTCToLocalTime, this function converts local time to UTC time.

select dbo.fn_LocalTimeToUTC(GetDate())

Returns: 2009-08-16 20:56:56.827

Conclusion

Today we started covering the user-defined SQL functions found inside CRM 4.0 database. Granted, sometimes the data that is returned doesn’t quite look right because all dates and times are in UTC format. I just have to say that you need to trust the results because this is what CRM uses on an everyday basis.

If you are ever confused by the results, I would suggest wrapping your query in a dbo.fn_UTCToLocalTIme() function to see what is returned. Like this:

select dbo.fn_UTCToLocalTime(dbo.fn_BeginOfLastSevenDay(GetUTCDate()))

Returns: 2009-08-09 00:00:00.000