SQL Server Date Time Calculation Examples
By: Jeffrey Yao | Comments (3) | Related: More > Dates
Problem
There are many SQL Server datetime related questions, for example, how do I find the second Wednesday of each month, or how do I find the first weekend day in a month? How many weekends do I have in a quarter? The list can go on and on. Is there any way that I can answer these questions easily?
Solution
There are many different ways via complex and sometimes hard-to-understand subqueries of dateadd / datediff combinations. But an easy way is to have a calendar table, and then figure out all these questions via intuitive queries.
This solution has a few points of interests
- Design a Calendar table with sufficient information for those questions and also to reduce query complexity and length
- Populate this table quickly with full spectrum data from 1900-01-01 to 9999-12-31 (if needed)
- Use the latest analytic functions in SQL Server 2012, i.e. First_Value, Last_value to simplify queries
We will first design a calendar table which will have [Year], [Quarter], [Month], [Week], [Week Number in A month], [Week Day], [Date]
USE TEMPDB IF OBJECT_ID('dbo.#t') is not null DROP TABLE dbo.#t; CREATE TABLE #t ([Date] datetime, [Year] smallint, [Quarter] tinyint, [Month] tinyint , [Day] smallint -- from 1 to 366 = 1st to 366th day in a year , [Week] tinyint -- from 1 to 54 = the 1st to 54th week in a year; , [Monthly_week] tinyint -- 1/2/3/4/5=1st/2nd/3rd/4th/5th week in a month , [Week_day] tinyint -- 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun ); GO
Next we will populate the table:
USE TEMPDB -- populate the table #t, and the day of week is defined as -- 1=Mon, 2=Tue, 3=Wed, 4=Thu,5=Fri, 6=Sat, 7=Sun ;WITH C0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)), C1 AS (SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B), C2 AS (SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B), C3 AS (SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B), C4 AS (SELECT 1 AS c FROM C3 AS A CROSS JOIN C3 AS B), C5 AS (SELECT 1 AS c FROM C4 AS A CROSS JOIN C3 AS B), C6 AS (select rn=row_number() over (order by c) from C5), C7 as (select [date]=dateadd(day, rn-1, '19000101') FROM C6 WHERE rn <= datediff(day, '19000101', '99991231')+1) INSERT INTO #t ([year], [quarter], [month], [week], [day], [monthly_week], [week_day], [date]) SELECT datepart(yy, [DATE]), datepart(qq, [date]), datepart(mm, [date]), datepart(wk, [date]) , datediff(day, dateadd(year, datediff(year, 0, [date]), 0), [date])+1 , datepart(week, [date]) -datepart(week, dateadd(month, datediff(month, 0, [date]) , 0))+1 , CASE WHEN datepart(dw, [date])+@@datefirst-1 > 7 THEN (datepart(dw, [date])+@@datefirst-1)%7 ELSE datepart(dw, [date])+@@datefirst-1 END , [date] FROM C7 --where [date] between '19900101' and '20990101'; -- if you want to populate a range of dates GO
A list of common questions
- Find the first/last day of previous/current/next month
- Find the first/last week/weekend day of previous/current/next month
- Find the first/last week/weekend day of each month of all years
- Find how many weekdays between two dates
- Find how many week/weekend days between two dates
- Find the N-th week/weekend day of previous/current/next month
- Find the N-th last week/weekend day of previous/current/next month
The following code demonstrates possible solutions to the questions listed above
-- find first day of prev/curr/next month with current date declare @curr_dt datetime = '2015-02-11'; select distinct First_Mth_Day=first_value([Date]) over (partition by [month] order by [Date] asc) , Last_Mth_Day=last_value([Date]) over (partition by [month] order by [Date] asc ROWS BETWEEN Current Row AND UNBOUNDED FOLLOWING ) from #t where datediff(month, @curr_dt, [Date]) between -1 and 1 and datediff(year, @curr_dt, [Date]) between -1 and 1 order by 1 go -- find the first/last weekday of prev/curr/next month declare @curr_dt datetime = '2015-02-11'; select distinct First_Week_Day=first_value([Date]) over (partition by [month] order by [Date] asc) , Last_Week_Day=last_value([Date]) over (partition by [month] order by [Date] asc ROWS BETWEEN Current Row AND 31 FOLLOWING ) from #t where datediff(month, @curr_dt, [Date]) between -1 and 1 and datediff(year, @curr_dt, [Date]) between -1 and 1 and Week_Day between 1 and 5 order by 1 go -- find the first / last weekend day of prev/curr/next month declare @curr_dt datetime = '2015-02-11'; with c as ( select distinct First_Wknd_Day=first_value([Date]) over (partition by [month] order by [Date] asc) , Last_Wknd_Day=last_value([Date]) over (partition by [month] order by [Date] asc ROWS BETWEEN Current Row AND UNBOUNDED FOLLOWING ) from #t where datediff(month, @curr_dt, [Date]) between -1 and 1 and datediff(year, @curr_dt, [Date]) between -1 and 1 and Week_Day between 6 and 7 ) select [Month]=choose((row_number() over (order by First_wknd_day asc)), 'Prev_Mth', 'Curr_Mth', 'Next_Mth') , First_Wknd_Day, Last_Wknd_day from c go -- find the first/last weekday in each month of all years select distinct [Year], [Month], First_Week_Day=first_value([Date]) over (partition by [year], [month] order by [Date] asc) , Last_Week_Day=last_value([Date]) over (partition by [year], [month] order by [Date] asc ROWS BETWEEN Current Row AND 32 FOLLOWING ) from #t where Week_Day between 1 and 5 -- for weekend, change to: between 6 and 7 order by 1, 2 go -- how many weekend days or weekdays between two dates declare @start_day datetime ='2015-01-11', @End_day datetime ='2015-02-03' select [Start_Date] = @Start_day, [End_Date]=@End_day, Total_weekend_days = count(*) from #t where (Week_Day between 6 and 7) -- for weekdays, use "between 1 and 5" and ([Date] between @start_day and @end_day) go -- find nth week/weekend day of each prev/curr/next month -- eg. find the 2nd Monday of each prev/curr/next month declare @curr_dt datetime = getdate(); ; with c as (select rn=RANK() over (partition by [month] order by [date] ASC), [Date] -- attention to 'ASC' from #t where datediff(month, @curr_dt, [Date]) between -1 and 1 and datediff(year, @curr_dt, [Date]) between -1 and 1 and Week_Day = 1 -- 1=Mon, 2=Tue,... 7=Sun ) select [Month]=choose((row_number() over (order by (select null))), 'Prev_Mth', 'Curr_Mth', 'Next_Mth') ,[2nd_Monday] = [Date] from c where rn=2 -- nth, for example if finding the 3rd Monday, set rn=3 order by 2 go -- find the nth last week/weekend day of prve/curr/next month -- eg. find the 2nd last Sat of prev/curr/next month declare @curr_dt datetime = getdate(); ; with c as (select rn=RANK() over (partition by [month] order by [date] DESC), [Date] -- attention to 'DESC' from #t where datediff(month, @curr_dt, [Date]) between -1 and 1 and datediff(year, @curr_dt, [Date]) between -1 and 1 and Week_Day = 6 -- 1=Mon, 2=Tus, ... 7=Sun ) select [Month]=choose((row_number() over (order by (select null))), 'Prev_Mth', 'Curr_Mth', 'Next_Mth') , [2nd_Last_Sat]=[Date] from c where rn=2 order by 2 go
Here are the query results:
Exercise Questions
With the calendar table, please solve the following questions
- Find the 2nd Tuesday of each quarter from year 2010 to 2020 (inclusive)
- Given the current datetime, find the Monday of previous/current/next week
- Calculate how many weekends you will enjoy until you retire (assume you retire at your 65th birthday)
Next Steps
DateTime calculation is a frequent task in DBA work, there are many different ways to achieve the same result. You can read a few more articles listed below to better understand different approaches to some common questions and also better understand the codes in this tip.
- Most queries are for SQL Server 2012 and later versions, but you may modify it to be used by SQL Server 2005 and later
- SQL Server 2012 Functions - First_Value and Last_Value
- SQL Server Date function that determines date range based on weekday
- Itzik Ben-Gan’s series articles here: (DateTime Calculations)
- Fun in Datetime Calculations
- Check out all of the MSSQLTips.com Date Tips
About the author

View all my tips