Jun 13, 2006

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
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)

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
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


Anonymous said...

Love the function addworkingdays. How would it be done if you wanted to go back a number of days?

Anonymous said...

select ((dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)-((datepart(dw,dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)+ @@datefirst - 2)%7 + 1)/5 *((datepart(dw,'1 sep 2007')+ @@datefirst - 2)%7 + 1)%5)

(consolidation of last working day of month in 1 stmt)

gives 29 sept, which is wrong, should be 28 sept

Omnibuzz said...

I am not sure how you arrived at the consolidated query, but this is how it will be if I have to do it

select ((dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)-
((datepart(dw,dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)+ @@datefirst - 2)%7 + 1)/5 *
(((datepart(dw,dateadd(mm,datediff(mm,0,'1 sep 2007') +1,0) - 1)+ @@datefirst - 2)%7 + 1))%5)

The above query gives the value as 28th sept which is right. Let me know if I am wrong and I will be happy to correct it.

bosm said...

The Addworkingsdays is very usefull. Do you also have a function who can make a date from a week and year, for the european calendar (datefirst=1). It hase to look like this "Create function Week2Date (@Week INT, @Year INT) as DATETIME....."
The problem I have, that I can't use the "Set datefirst 1" in a function, witch create a wrong week nr for some dates.

Post a Comment