First and Last Date of Month in T-SQL

Here are two functions which my esteemed colleague Brandon Turgeon came up with:

if exists(select object_id from sys.objects where object_id=object_id(N'[dbo].[ufn_getfirstdayofmonth]’))
drop function ufn_getfirstdayofmonth
go
create function ufn_getfirstdayofmonth
(
@dateinmonth datetime
)
returns datetime
as
begin
declare @first_current_month datetime
declare @first_current_month_hour datetime
declare @first_current_month_min datetime
declare @first_current_month_sec datetime
declare @firstdayofmonth datetime
SELECT @first_current_month = DATEADD(dd, -(DAY(DATEADD(m,0,@dateinmonth))-1), @dateinmonth)
SELECT @first_current_month_hour = DATEADD(hh, -(DATEPART(hour, @first_current_month)), @first_current_month)
SELECT @first_current_month_min = DATEADD(mi, -(DATEPART(minute, @first_current_month_hour)), @first_current_month_hour)
SELECT @first_current_month_sec = DATEADD(ss, -DATEPART(second, @first_current_month_min), @first_current_month_min)
SELECT @firstdayofmonth = DATEADD(millisecond, -DATEPART(millisecond, @first_current_month_sec), @first_current_month_sec)
return @firstdayofmonth
end
go

if exists(select object_id from sys.objects where object_id=object_id(N'[dbo].[ufn_getlastdayofmonth]’))
drop function ufn_getlastdayofmonth
go
create function ufn_getlastdayofmonth
(
@dateinmonth datetime
)
returns datetime
as
begin
declare @first_next_month datetime
declare @first_next_month_hour datetime
declare @first_next_month_min datetime
declare @last_this_month_sec datetime
declare @lastdayofmonth datetime
SELECT @first_next_month = DATEADD(dd, -(DAY(DATEADD(m,1,@dateinmonth))-1), DATEADD(m,1,@dateinmonth))
SELECT @first_next_month_hour = DATEADD(hh, -(DATEPART(hour, @first_next_month)), @first_next_month)
SELECT @first_next_month_min = DATEADD(mi, -(DATEPART(minute, @first_next_month_hour)), @first_next_month_hour)
SELECT @last_this_month_sec = DATEADD(ss, -(DATEPART(ss, @first_next_month_min)+1), @first_next_month_min)
SELECT @lastdayofmonth = DATEADD(millisecond, -(DATEPART(millisecond, @last_this_month_sec)), @last_this_month_sec)
return @lastdayofmonth
end
go

And look here’s the test script:

declare @dateinmonth datetime;
set @dateinmonth = getdate();
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date
set @dateinmonth = ‘2008-02-29 12:04:02.113’;
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date
set @dateinmonth = ‘2008-03-01 12:04:02.113’;
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date
set @dateinmonth = ‘2007-02-21 00:00:00.003’;
select dbo.ufn_getfirstdayofmonth(@dateinmonth) as [first date of month for], @dateinmonth as date
select dbo.ufn_getlastdayofmonth(@dateinmonth) as [last date of month for], @dateinmonth as date

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s