Date Functions in SQL Server - FAQ

There are lots of queries on date operations like getting the first day of the month or week or quarter, last day given some date. So I thought I will put over here some basic implementations.
I will keep updating the post as and when I get to solve more queries on this.

My input is a datetime variable. And I assign the current date to it.

declare @a datetime
set @a = getdate()

Before I get into writing the implementation I am setting the datefirst to remove any ambiguity on my frame of reference on the start of the week

set datefirst 7

The following are some operations that you might want to do on date fields.

select dateadd(mm,datediff(mm,0,@a),0) as first_day_of_month

For the above query, I can change 'mm' to 'yy' or 'qq' or to get the first day of the year or quarter.

select dateadd(mm,datediff(mm,0,@a)+ 1,0)-1 as last_day_of_month

select dateadd(mm,datediff(mm,0,@a),0) + 8-
datepart(dw,dateadd(mm,datediff(mm,0,@a),0)) as first_sunday_of_month

select dateadd(mm,datediff(mm,0,@a)+ 1,0)-1 -
datepart(dw,dateadd(mm,datediff(mm,0,@a) + 1,0)-2) as last_sunday_of_month

select dateadd(wk,datediff(wk,0,@a)-2, 0) - 1 as last_week_begin ,dateadd(wk,datediff(wk,0,@a)-1, 0)-2 as last_week_end


The below set of queries can be used to find the begining and end (monday-friday) date of the weeks for an accepted time period. It will partition the week into two if the week spans across two months - Week begin to month end & next month begin to week end. The one given below will find all weeks between 2006 to 2050. I know that the query will look like a monster :) Apologies for not commenting it.


set dateformat mdy
set datefirst 7

declare @startdate datetime,
@enddate datetime
set @startdate = '2006-01-01'
set @enddate = '2050-01-01'
select top 30000 identity(int,0,1) as id into #temp from sysobjects a,sysobjects b,sysobjects c

select dateadd(dd, a.id,@startdate) as sow, dateadd(dd, b.id,@startdate) as eow from #temp a, #temp b
where datediff(day,dateadd(dd, a.id,@startdate),
dateadd(dd, b.id,@startdate)) between 0 and 4
and (
(datepart(dw, dateadd(dd, a.id,@startdate)) = 2 and datepart(dw, dateadd(dd, b.id,@startdate)) = 6 )
or (datepart(dw, dateadd(dd, a.id,@startdate)) = 2 and datepart(dd,dateadd(dd, b.id,@startdate)) =
datepart(dd, dateadd(dd,-1,cast( cast( year(dateadd(dd, b.id,@startdate))+ month(dateadd(dd, b.id,@startdate))/12 as varchar) + '-' + cast(month(dateadd(dd, b.id,@startdate))%12 + 1 as varchar) + '-01' as datetime))))
or (datepart(dw, dateadd(dd, b.id,@startdate)) = 6 and datepart(dd, dateadd(dd, a.id,@startdate)) = 1 ))
and datepart(mm, dateadd(dd, a.id,@startdate)) = datepart(mm, dateadd(dd, b.id,@startdate))
and dateadd(dd, b.id,@startdate) <= @enddate and dateadd(dd, a.id,@startdate) <= @enddate order by dateadd(dd, a.id,@startdate) drop table #temp


To find number of saturdays and sundays between two given dates:

declare @a datetime, @b datetime
--you set the number of daysto subtract to test
set @a = '2006-01-01'
set @b = '2007-01-01'
-- find out what date range you have set
select @a as [start date],datename(dw,@a) as start_dow,datepart(dw,@a) as start,
datename(dw,@b) as end_dow,datepart(dw,@b) as [end],@b as [end date]
select datediff(ww,@a,@b) as [no of weeks difference],datediff(dd,@a,@b) as [no of days difference]
select datediff(ww,@a,@b) + case when datename(dw,@a) = 'sunday' then 1 else 0 end as [no of sundays],
datediff(ww,@a,@b) + case when datename(dw,@b) = 'saturday' then 1 else 0 end as [no of saturdays]

The following function will get me the last working day (assuming saturday and sunday are holidays) for the month of any given input date. The function is invariant to datefirst server setting.


create function LastWorkingDayOfMonth(@InputDate datetime)
returns datetime
as
begin
declare @lastdayofmnth datetime,
@DOWwithMondayBegin int

set @lastdayofmnth = dateadd(mm,datediff(mm,0,@InputDate) +1,0) - 1
set @DOWwithMondayBegin = (datepart(dw,@lastdayofmnth)+ @@datefirst - 2)%7 + 1

return (@lastdayofmnth-@DOWwithMondayBegin/5 *@DOWwithMondayBegin%5)
end

The following function will add the number of working days (assuming saturday and sunday are holidays)specified, to the start date. This function is invariant to the datefirst server setting

create function addworkingdays(@StartDate datetime,@days int)
returns datetime
as
begin
declare @beginOfWeek datetime,
@NoOfDaysBefore int,
@NoOfWholeWeeks int
set @NoOfDaysBefore = (datepart(dw,@StartDate) + @@datefirst - 2)%7
set @beginOfWeek =
@StartDate - @NoOfDaysBefore -- get the corresponding monday of the week

set @days = @days +
case when @NoOfDaysBefore < 5 then @NoOfDaysBefore else 4 end--add only working days return @beginOfWeek + @days/5*7 + @days%5 end