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