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 )