# SQL Server Date Time Calculation Examples

By:   |   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])+@@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: ## 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. 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

 Saturday, September 9, 2017 - 11:05:46 PM - stephen E Okala Back To Top (66116) 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 (36321) @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 (36318) How would the script that populates the temp table be modified so it would work in SQL Server 2005?