Using built-in CRM functions when writing SQL Reports

Posted on February 20th, 2009 mitch  Print 2 comments

If you've ever worked with CRM's Advanced Find you know what an amazing number of built-in date query parameters there are.  It turns out, that those query parameters have corresponding user-defined SQL functions that you can use in your own queries for operations such as custom reports.

Here is a list of some of the more common date functions:

fn_BeginOfDay
fn_BeginOfHour
fn_BeginOfLastMonth
fn_BeginOfLastSevenDay
fn_BeginOfLastWeek
fn_BeginOfLastXDay
fn_BeginOfLastXHour
fn_BeginOfLastXWeek
fn_BeginOfLastYear
fn_BeginOfMonth
fn_BeginOfNextMonth
fn_BeginOfNextWeek
fn_BeginOfNextYear
fn_BeginOfThisMonth
fn_BeginOfThisWeek
fn_BeginOfThisYear
fn_BeginOfToday
fn_BeginOfTomorrow
fn_BeginOfWeek
fn_BeginOfYear
fn_BeginOfYesterday
fn_EndOfLastMonth
fn_EndOfLastWeek
fn_EndOfLastYear
fn_EndOfNextMonth
fn_EndOfNextSevenDay
fn_EndOfNextWeek
fn_EndOfNextXDay
fn_EndOfNextXHour
fn_EndOfNextXWeek
fn_EndOfNextYear
fn_EndOfThisMonth
fn_EndOfThisWeek
fn_EndOfThisYear
fn_EndOfToday
fn_EndOfTomorrow
fn_EndOfYesterday
fn_FirstDayOfMonth
fn_LastXMonth
fn_LastXYear
fn_LocalTimeToUTC
fn_NextXMonth
fn_NextXYear
fn_NTDayOfWeek

If you would like to see these functions in action, copy the following script into SQL Management Studio and run it ( after connecting to the CRM database ):

select 'fn_BeginOfDay' as "Function",  dbo.fn_BeginOfDay(GetDate()) as "Value"
union
select 'fn_BeginOfHour',  dbo.fn_BeginOfHour(GetDate())
union
select 'fn_BeginOfLastMonth',  dbo.fn_BeginOfLastMonth(GetDate())
union
select 'fn_BeginOfLastSevenDay',  dbo.fn_BeginOfLastSevenDay(GetDate())
union
select 'fn_BeginOfLastWeek',  dbo.fn_BeginOfLastWeek(GetDate())
union
select 'fn_BeginOfLastXDay',  dbo.fn_BeginOfLastXDay(GetDate(), 1)
union
select 'fn_BeginOfLastXHour',  dbo.fn_BeginOfLastXHour(GetDate(), 1)
union
select 'fn_BeginOfLastXWeek',  dbo.fn_BeginOfLastXWeek(GetDate(), 2)
union
select 'fn_BeginOfLastYear',  dbo.fn_BeginOfLastYear(GetDate())
union
select 'fn_BeginOfMonth',  dbo.fn_BeginOfMonth(GetDate())
union
select 'fn_BeginOfNextMonth',  dbo.fn_BeginOfNextMonth(GetDate())
union
select 'fn_BeginOfNextWeek',  dbo.fn_BeginOfNextWeek(GetDate())
union
select 'fn_BeginOfNextYear',  dbo.fn_BeginOfNextYear(GetDate())
union
select 'fn_BeginOfThisMonth',  dbo.fn_BeginOfThisMonth(GetDate())
union
select 'fn_BeginOfThisWeek',  dbo.fn_BeginOfThisWeek(GetDate())
union
select 'fn_BeginOfThisYear',  dbo.fn_BeginOfThisYear(GetDate())
union
select 'fn_BeginOfToday',  dbo.fn_BeginOfToday(GetDate())
union
select 'fn_BeginOfTomorrow',  dbo.fn_BeginOfTomorrow(GetDate())
union
select 'fn_BeginOfWeek',  dbo.fn_BeginOfWeek(GetDate())
union
select 'fn_BeginOfYear',  dbo.fn_BeginOfYear(GetDate())
union
select 'fn_BeginOfYesterday',  dbo.fn_BeginOfYesterday(GetDate())
union
select 'fn_EndOfLastMonth',  dbo.fn_EndOfLastMonth(GetDate())
union
select 'fn_EndOfLastWeek',  dbo.fn_EndOfLastWeek(GetDate())
union
select 'fn_EndOfLastYear',  dbo.fn_EndOfLastYear(GetDate())
union
select 'fn_EndOfNextMonth',  dbo.fn_EndOfNextMonth(GetDate())
union
select 'fn_EndOfNextSevenDay',  dbo.fn_EndOfNextSevenDay(GetDate())
union
select 'fn_EndOfNextWeek',  dbo.fn_EndOfNextWeek(GetDate())
union
select 'fn_EndOfNextXDay',  dbo.fn_EndOfNextXDay(GetDate(), 1)
union
select 'fn_EndOfNextXHour',  dbo.fn_EndOfNextXHour(GetDate(), 1)
union
select 'fn_EndOfNextXWeek',  dbo.fn_EndOfNextXWeek(GetDate(), 1)
union
select 'fn_EndOfNextYear',  dbo.fn_EndOfNextYear(GetDate())
union
select 'fn_EndOfThisMonth',  dbo.fn_EndOfThisMonth(GetDate())
union
select 'fn_EndOfThisWeek',  dbo.fn_EndOfThisWeek(GetDate())
union
select 'fn_EndOfThisYear',  dbo.fn_EndOfThisYear(GetDate())
union
select 'fn_EndOfToday',  dbo.fn_EndOfToday(GetDate())
union
select 'fn_EndOfTomorrow',  dbo.fn_EndOfTomorrow(GetDate())
union
select 'fn_EndOfYesterday',  dbo.fn_EndOfYesterday(GetDate())
union
select 'fn_FirstDayOfMonth',  dbo.fn_FirstDayOfMonth(GetDate(), 10)
union
select 'fn_LastXMonth',  dbo.fn_LastXMonth(GetDate(), 1)
union
select 'fn_LastXYear',  dbo.fn_LastXYear(GetDate(), 1)
union
select 'fn_NextXMonth',  dbo.fn_NextXMonth(GetDate(), 1)
union
select 'fn_NextXYear',  dbo.fn_NextXYear(GetDate(), 1)

Customization, Dynamics CRM, Reporting

(No Ratings Yet)

 Loading ...

817 views