Sunday, May 29, 2011

SQL Tips: Faster technique to get 1st and last day of the month

Normally we put together the year, month and 1 as the day separating them with slashes (/) in the date format (e.g “YYYY/MM/DD” ).
Like following code:-
CREATE FUNCTION [dbo].[GetFirstDayOfMonth] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + ‘/’ + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + ‘/01′ AS DATETIME)
END
GO
 In this function we need to separate month year from InputDate and then convert them into string.
This string conversion operation is time taking and consume good amount of memory as well.
I came up with a solution that easily return 1stdate of current moth.
This technique doesn’t need the conversion to string and back it to date therefore somewhat faster than normal technique:
SELECT DATEADD(month, DATEDIFF(month, ‘20010101′, getdate()), ‘20010101′) as firstDate(where ‘20010101′ is a randomly chosen date - most any date will work).For instance, to get the last day of the month we need to change only the second occurrence:
SELECT DATEADD( month, DATEDIFF(month, ‘20010101′, getdate()), ‘20010131′ ) as lastDate.