The final installment in this series contains miscellaneous SQL functions.
You may wish to review the following articles before proceeding:
CRM 4.0’s Built-in SQL Functions (Part 1 of 5)
CRM 4.0’s Built-in SQL Functions (Part 2 of 5)
CRM 4.0’s Built-in SQL Functions (Part 3 of 5)
CRM 4.0’s Built-in SQL Functions (Part 4 of 5)
dbo.fn_FindBusinessGuid
This function will return the ID for the business unit for the currently logged-on user.
select dbo.fn_FindBusinessGuid()
dbo.fn_FindUserGuid
This function will return the CRM ID for the currently logged-on user.
select dbo.fn_FindUserGuid()
Conclusion
I hope you enjoyed this series which explored the built-in user-defined functions included with CRM 4.0. Hopefully, it will make your report writing quicker and more powerful.
I have a couple of additional articles planned that will cover some of these functions in more detail in the future.
In this section, we’ll cover year-related functions.
You may wish to review the following articles before proceeding:
CRM 4.0’s Built-in SQL Functions (Part 1 of 5)
CRM 4.0’s Built-in SQL Functions (Part 2 of 5)
CRM 4.0’s Built-in SQL Functions (Part 3 of 5)
dbo.fn_BeginOfLastYear
Returns the beginning date and time for last year.
This function requires a valid datetime value.
select dbo.fn_BeginOfLastYear(GetUTCDate())
Returns: 2008-01-01 06:00:00.000
dbo.fn_BeginOfNextYear
Returns the beginning date and time for next year.
This function requires a valid datetime value.
select dbo.fn_BeginOfNextYear(GetUTCDate())
Returns: 2010-01-01 06:00:00.000
dbo.fn_BeginOfThisYear
Returns the beginning date and time for this year.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_BeginOfThisYear(GetUTCDate())
Returns: 2009-01-01 06:00:00.000
dbo.fn_BeginOfYear
Returns the beginning date and time of a year.
This function requires a valid datetime value.
select dbo.fn_BeginOfYear(GetUTCDate())
Returns: 2009-01-01 06:00:00.000
dbo.fn_EndOfLastYear
Returns the ending date and time for last year.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfLastYear(GetUTCDate())
Returns: 2009-01-01 06:00:00.000
dbo.fn_EndOfNextYear
Returns the ending date and time for next year.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfNextYear(GetUTCDate())
Returns: 2011-01-01 06:00:00.000
dbo.fn_EndOfThisYear
Returns the ending date and time for this year.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfThisYear(GetUTCDate())
Returns: 2010-01-01 06:00:00.000
dbo.fn_LastXYear
Returns the date from X number of years ago ( from today ).
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_LastXYear(GetUTCDate(), 1)
Returns: 2008-08-16 05:00:00.000
dbo.fn_NextXYear
Returns a date that is X number of years in the future ( from today )
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_NextXYear(GetUTCDate(), 1)
Returns: 2010-08-17 05:00:00.000
I have a new article on the CRM Team Blog that details how to calculate return on investment (ROI) for CRM customizations.
Today we are continuing on with part three of our series on using CRM 4.0’s built-in SQL functions.
You may wish to review the following articles:
CRM 4.0’s Built-in SQL Functions (Part 1 of 5)
CRM 4.0’s Built-in SQL Functions (Part 2 of 5)
Note: The date used in the following example is 2009-08-16.
dbo.fn_BeginOfLastMonth
Return the beginning date and time of last month.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_BeginOfLastMonth(GetUTCDate())
Returns: 2009-07-01 05:00:00.000
dbo.fn_BeginOfMonth
Return the beginning date and time of a specified month.
This function requires a valid datetime value.
select dbo.fn_BeginOfMonth(GetUTCDate())
Returns: 2009-08-01 05:00:00.000
dbo.fn_BeginOfNextMonth
Return the beginning of next month.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_BeginOfNextMonth(GetUTCDate())
Returns: 2009-09-01 05:00:00.000
dbo.fn_BeginOfThisMonth
Return the beginning of this month. This function has the same functionality as dbo.fn_BeginOfMonth.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_BeginOfThisMonth(GetUTCDate())
Returns: 2009-08-01 05:00:00.000
dbo.fn_EndOfLastMonth
Returns the ending date and time of last month.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfLastMonth(GetUTCDate())
Returns: 2009-08-01 05:00:00.000
dbo.fn_EndOfNextMonth
Returns the ending date and time of next month.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfNextMonth(GetUTCDate())
Returns: 2009-10-01 05:00:00.000
dbo.fn_EndOfThisMonth
Returns the ending date and time of this month.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfThisMonth(GetUTCDate())
Returns: 2009-09-01 05:00:00.000
dbo.fn_FirstDayOfMonth
Returns the first date of the month for a specified month.
This first parameter is a valid datetime value. The second parameter is the month you wish to check.
select dbo.fn_FirstDayOfMonth(GetUTCDate(), 8)
Returns: 2009-08-01 00:00:00.000
dbo.fn_LastXMonth
Returns the date and time from one month ago today ( assuming 30 days ago ).
This first parameter is a valid datetime value. The second parameter is the number of months in the past.
select dbo.fn_LastXMonth(GetUTCDate(), 1)
Returns: 2009-07-16 05:00:00.000
dbo.fn_NextXMonth
Returns the date and time from one month in the future – from today.
This first parameter is a valid datetime value. The second parameter is the number of months in the future.
select dbo.fn_NextXMonth(GetUTCDate(), 1)
Returns: 2009-09-17 05:00:00.000
I heard this on TV today:
A man has the right to make a fool of himself.
Oh yeah!
As we continue onto the second article in our set, you may wish to review the previous article:
CRM 4.0’s Built-in SQL Functions (Part 1 of 5)
Note: The date used in the following example is 2009-08-16.
dbo.fn_BeginOfLastWeek
Returns the beginning date and time for last week.
This function requires a valid datetime value. It is assumed you will specify today’s date and time
select dbo.fn_BeginOfLastWeek(GetUTCDate())
Returns: 2009-08-23 05:00:00.000
dbo.fn_BeginOfLastXWeek
Returns the beginning date and time for a week X weeks ago.
The first parameter is the date in question, the second parameter is the number of weeks before that date.
select dbo.fn_BeginOfLastXWeek(GetUTCDate(), 1)
Returns: 2009-08-09 05:00:00.000
dbo.fn_BeginOfNextWeek
Returns the beginning date and time for next week.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_BeginOfNextWeek(GetUTCDate())
2009-08-23 05:00:00.000
dbo.fn_BeginOfWeek
Returns the beginning date and time for a specified date.
This function requires a valid datetime value.
select dbo.fn_BeginOfWeek(GetUTCDate())
2009-08-16 05:00:00.000
dbo.fn_BeginOfThisWeek
Returns the beginning date and time for this week.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_BeginOfThisWeek(GetUTCDate())
Returns: 2009-08-16 05:00:00.000
dbo.fn_EndOfLastWeek
Returns the ending date and time for last week.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfLastWeek(GetUTCDate())
Returns: 2009-08-16 05:00:00.000
dbo.fn_EndOfNextWeek
Returns the ending date and time for nextweek.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfNextWeek(GetUTCDate())
Returns: 2009-08-30 05:00:00.000
dbo.fn_EndOfNextXWeek
Returns the beginning date and time for a week X weeks in the future.
The first parameter is the date in question, the second parameter is the number of weeks after that date.
select dbo.fn_EndOfNextXWeek(GetUTCDate(), 1)
Returns: 2009-08-24 05:00:00.000
dbo.fn_EndOfThisWeek
Returns the ending date and time for this week.
This function requires a valid datetime value. It is assumed you will specify today’s date and time.
select dbo.fn_EndOfThisWeek(GetUTCDate())
Returns: 2009-08-23 05:00:00.000
dbo.fn_NTDayOfWeek
Returns the day of the week. 0 = Sunday, 6 = Saturday.
select dbo.fn_NTDayOfWeek(GetUTCDate())
Returns: 0 ( for Sunday )
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.
When creating solutions that utilize the CRM 4.0 SDK I often find myself needing to quickly create and test a piece of code that allows me perform a specific function.
I’ve created a simple application skeleton to aid in my development efforts. It has the code required to connect to CRM and display the results of whatever code I’m writing. It is a simple Visual Studio 2008 Windows Forms solution that contains a list box, a large text box and a couple of buttons. I use the list box and text box to display the results of whatever test I’m working on.
I find this skeleton especially useful when developing plugins because I need to write and test the code outside of the plugin environment. Once I’m sure that the code is functioning the way I wish, I simply copy and paste it into the plugin.
You can find this skeleton in the Free Utilities section of this blog.
is just another name for Love…
One of my customers has a fairly interesting process that I thought I would share with you.
They move activities from one department to another by setting a Current Stage field to indicate the current state of the work being performed on the activity. It looks something like this:
Routing Activities to Queues
On of the rather interesting things about the CRM 4.0 workflow process is that you can actually route activities to a queue by simply assigning the activity to the desired queue. I use that technique in the following workflow:
This workflow works using the following criteria:
- When an email is created
- When the attribute Current Stage is modified
The workflow will review the value of the Current Stage attribute then assign the email to the appropriate queue.
Note: To make things easier for the user and administrator, we make the Current Stage picklist values and the names of the queues the same.
For each activity type that you need this process performed on, you’ll need to add the Current Stage attribute to the form, then create a corresponding workflow rule to handle the routing.
Conclusion
While this solution may not seem like much at first, it does showcase the power of the CRM 4.0 workflow process and can save small amounts of time that add up to big overall savings.
Loading ...
