CRM 4.0’s Built-in SQL Functions (Part 1 of 5)

Posted on August 17th, 2009 Mitch Milam  Print 4 comments

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.

Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

1,666 views