Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Date Time Calculation Examples


By:   |   Read Comments (3)   |   Related Tips: More > Dates

Attend a SQL Server Conference for FREE >> click to learn more


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])+@@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

  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]=@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:

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


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



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?


Learn more about SQL Server tools