SQL Server Date Time Calculation Examples


By:   |   Updated: 2015-02-13   |   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

  1. Design a Calendar table with sufficient information for those questions and also to reduce query complexity and length
  2. Populate this table quickly with full spectrum data from 1900-01-01 to 9999-12-31 (if needed)
  3. 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])[email protected]@datefirst-1 > 7 THEN (datepart(dw, [date])[email protected]@datefirst-1)%7
         ELSE datepart(dw, [date])[email protected]@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

  1. Find the first/last day of previous/current/next month
  2. Find the first/last week/weekend day of previous/current/next month
  3. Find the first/last week/weekend day of each month of all years
  4. Find how many weekdays between two dates
  5. Find how many week/weekend days between two dates
  6. Find the N-th week/weekend day of previous/current/next month
  7. 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][email protected]_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:

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.



Last Updated: 2015-02-13


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Saturday, September 09, 2017 - 11:05:46 PM - stephen E Okala Back To Top

 Awsome! But one quick question. How do I handle multiple calendar in one date dimension table?. I want to build one with four fiscal-year calendars.

Thanks

Stephen

 


Monday, February 23, 2015 - 1:21:38 PM - jeff_yao Back To Top

@jdguilty, all you need to do is replace

VALUES(1), (1)

WITH

SELECT (1) UNION ALL SELECT (1)

 

thanks for your feedback.

jyao


Monday, February 23, 2015 - 10:12:54 AM - jdguilty Back To Top

How would the script that populates the temp table be modified so it would work in SQL Server 2005?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools