As I mentioned in a previous article, CRM 4.0 includes a group of user-defined functions that are commonly used within SQL Reporting Services reports to query CRM data. I first noticed these functions when modifying a report created using the CRM Report Wizard.
That got me to thinking that I needed to explore these functions in more detail to determine if they would be of use to me when producing my own reports or simply when creating custom SQL queries to query CRM data.
Since there are so many functions, I’ve decided to break our discussion into five articles so as not to overwhelm you with data.
Overview
The first thing you will notice is that most of the functions work with dates and times. This is great for us because most date math can be one of the hardest topics to master and it also makes your SQL queries difficult to read.
The second point that requires attention is the fact that all dates within CRM are stored in Coordinated Universal Time, or UTC, as it is called. This was done ( I’m sure ) to allow for a consistent method for performing date calculations without requiring the developer to take time zones into account. The only issue is when a human looks at the dates, they look funny because they are not reconciled with the local time zone and don’t match what a person thinks the date and time should be.
So, let’s get started with our review.
Notes:
I will be using Eastern Daylight Savings Time for these articles. This is UTC –5 hours.
You should also note that UTC times are in 24-hour format, not 12-hour format.
In my examples, I’ll be using the SQL function GetUTCDate() which returns the current date and time in UTC format.
It would also appear that the beginning of one day is actually the same as the ending of the previous day.
Today’s date is 2009-08-16
dbo.fn_BeginOfDay
Returns the date and time of the beginning time for a specified day. The day starts at midnight ( 00:00 ).
This function requires a valid datetime value.
select dbo.fn_BeginOfDay(GetUTCDate())
Returns the value: 2009-08-16 05:00:00.000
dbo.fn_BeginOfLastXDay
Returns the beginning of a day, X number of days in the past.
The first parameter is the date in question, the second parameter is the number of days prior to that date.
select dbo.fn_BeginOfLastXDay(GetUTCDate(), 1)
Returns: 2009-08-15 05:00:00.000
dbo.fn_BeginOfToday
Returns the beginning of today.
This function requires a valid datetime value.
select dbo.fn_BeginOfToday(GetUTCDate())
Returns: 2009-08-16 05:00:00.000
dbo.fn_BeginOfTomorrow
Returns the beginning of tomorrow.
This function requires a valid datetime value. It is assumed you will pass GetUTCDate() to properly retrieve today’s date.
select dbo.fn_BeginOfTomorrow(GetUTCDate())
Returns: 2009-08-17 05:00:00.000
dbo.fn_BeginOfYesterday
Returns the beginning of yesterday.
This function requires a valid datetime value. This function is actually the same as dbo.fn_BeginOfDay(). It is assumed you will pass GetUTCDate() to properly retrieve today’s date..
select dbo.fn_BeginOfYesterday(GetUTCDate())
Returns: 2009-08-15 05:00:00.000
dbo.fn_EndOfNextSevenDay
Returns the beginning of the day for a date 7 days into the future.
This function requires a valid datetime value.
select dbo.fn_EndOfNextSevenDay(GetUTCDate())
Returns: 2009-08-24 05:00:00.000
dbo.fn_EndOfNextXDay
Returns the beginning of the day for the day after a day X days into the future.
The first parameter is the date in question, the second parameter is the number of days after that date.
select dbo.fn_EndOfNextXDay(GetUTCDate(), 1)
Returns: 2009-08-18 05:00:00.000
dbo.fn_BeginOfLastSevenDay
Returns the beginning of the day, for a day 7 days into the past.
This function requires a valid datetime value.
select dbo.fn_BeginOfLastSevenDay(GetUTCDate())
Returns: 2009-08-09 05:00:00.000
dbo.fn_EndOfToday
Returns the end of today.
This function requires a valid datetime value. It is assumed you will pass GetUTCDate() to properly retrieve today’s date.
select dbo.fn_EndOfToday(GetUTCDate())
Returns: 2009-08-17 05:00:00.000
dbo.fn_EndOfTomorrow
Returns the end of tomorrow.
This function requires a valid datetime value. It is assumed you will pass GetUTCDate() to properly retrieve today’s date.
select dbo.fn_EndOfTomorrow(GetUTCDate())
Returns: 2009-08-18 05:00:00.000
dbo.fn_EndOfYesterday
Returns the end of yesterday.
This function requires a valid datetime value. It is assumed you will pass GetUTCDate() to properly retrieve today’s date.
select dbo.fn_EndOfYesterday(GetUTCDate())
Returns: 2009-08-16 05:00:00.000
dbo.fn_BeginOfHour
Returns the beginning of the current hour.
This function requires a valid datetime value. It is assumed you will pass GetUTCDate() to properly retrieve today’s date and time.
select dbo.fn_BeginOfHour(GetUTCDate())
Returns: 2009-08-16 17:00:00.000
dbo.fn_BeginOfLastXHour
Returns the beginning of the last hour, X hours in the past.
The first parameter is the date/time in question, the second parameter is the number of hours previous to the current time.
select dbo.fn_BeginOfLastXHour(GetUTCDate(), 1)
Returns: 2009-08-16 16:00:00.000
dbo.fn_EndOfNextXHour
Returns the end of the next hour, X hours into the future.
The first parameter is the date/time in question, the second parameter is the number of hours previous to the current time.
select dbo.fn_EndOfNextXHour(GetUTCDate(), 1)
Returns: 2009-08-16 19:00:00.000
All of this UTC stuff is giving me a headache!
Welcome to the club. Luckily, the CRM team gave us a function to covert everything to the local time zone:
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
Good luck.
