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

Article Last Updated: 2015-02-13