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

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 )

Leave a Reply 0 comments