Using T-SQL to Answer Date-Related Calculations

There are many interesting questions related to date calculations. For example, I have seen the following questions

• Give a date, find the first Tuesday day of the previous week/month/quarter, or the last day of next week/month/quarte (we can assume, last day of a week is Saturday)
• Find how many weekends between two dates (inclusive)
• Find the first / last working day of a month in which the current date is given (we assume workday is between Monday to Friday. We will ignore holidays here as holidays are “arbitrary” in that every culture may define their own holidays)
• Find the date of the Nth weekday in a month/quarter/year (eg. what date is the fifth Friday in quarter 3 of 2022).

There are many different ways to do the work, but I think a generic approach is better in any scenario.

The algorithm is simple as the following:

If the question is about calculating dates between two defined dates, i.e. @from and @to parameters, we will construct a calendar between @from and @to dates.

If the question is about calculating dates based on one defined date @current_date, we construct a calendar between (@current_date – 10 years) and (@current_date + 10 years). Of course, we can replace 10 years with any number of years we want (to meet your question requirements)

Now, let’s see some solutions to the questions mentioned above.

1. Today is Jan 27, 2022, please find the first Tuesday of previous month and the last day of next Month

-- find the first Tuesday of last month
declare @current_date datetime = '2022-01-27'; -- getdate()
declare @from datetime = dateadd(year, -10, @current_date)
, @to datetime = dateadd(year, 10, @current_date);
; with t(n) as (
select 1 as n union all select 1 as n)
, t2(n) as ( select 1 from t cross join  t as tn )
, t3(n) as ( select 1 from t2 cross join  t2 as tn )
, t4(n) as ( select 1 from t3 cross join  t3  as tn)
, t5(n) as ( select 1 from t4 cross join  t4  as tn)
, t6(n) as ( select 1 from t5 cross join  t5  as tn)
, t7(n) as (select n=row_number() over (order by n) from t6)
, t8 as (select [year] =  datepart(yy, @from+n-1)
, [Quarter] = datepart(qq, @from+n-1)
, [Month] = datepart(mm, @from+n-1)
, [DayOfYear] = datepart(dy, @from+n-1)
, [Day] = datepart(dd, @from+n-1)
, [Week] = datepart(ww, @from+n-1)
-- WeekDay: 1=Mon, Tue=2,...Sat=6, Sun= 7
-- not affected by @@datefirst settings
, [WeekDay] =case (datepart(dw, @from+n-1) +@@datefirst-1)%7 
when 0 then 7 
else (datepart(dw, @from+n-1) +@@datefirst-1)%7 
 end
, [iso_week] = datepart(isoww, @from+n-1)
, [Calendar_date] = @from + n -1
  from t7 where n <= datediff(day, @from, @to)+1)
, Calendar as (
select *, rn=row_number() over (partition by [year], [Month]  order by calendar_date asc ) 
from T8 
where [WeekDay] = 2 and
datediff(month, @current_date, Calendar_date)=-1
)
select  * from Calendar 
where rn = 1;

Please pay special attention to the high-lighted code, which is the key to the solution. For example, if the question is “find the last Tuesday of last Month”, then the code needs to be changed to the following

partition by [year], [Month]  order by calendar_date desc

2. Find the last day of last month

-- find the last day of last month
declare @current_date datetime = '2022-01-27'; -- getdate()
declare @from datetime = dateadd(year, -10, @current_date)
, @to datetime = dateadd(year, 10, @current_date);
; with t(n) as (
select 1 as n union all select 1 as n)
, t2(n) as ( select 1 from t cross join  t as tn )
, t3(n) as ( select 1 from t2 cross join  t2 as tn )
, t4(n) as ( select 1 from t3 cross join  t3  as tn)
, t5(n) as ( select 1 from t4 cross join  t4  as tn)
, t6(n) as ( select 1 from t5 cross join  t5  as tn)
, t7(n) as (select n=row_number() over (order by n) from t6)
, t8 as (select [year] =  datepart(yy, @from+n-1)
, [Quarter] = datepart(qq, @from+n-1)
, [Month] = datepart(mm, @from+n-1)
, [DayOfYear] = datepart(dy, @from+n-1)
, [Day] = datepart(dd, @from+n-1)
, [Week] = datepart(ww, @from+n-1)
-- WeekDay: 1=Mon, Tue=2,...Sat=6, Sun= 7
-- not affected by @@datefirst settings
, [WeekDay] =case (datepart(dw, @from+n-1) +@@datefirst-1)%7 
when 0 then 7 
else (datepart(dw, @from+n-1) +@@datefirst-1)%7 
 end
, [iso_week] = datepart(isoww, @from+n-1)
, [Calendar_date] = @from + n -1
  from t7 where n <= datediff(day, @from, @to)+1)
, Calendar as (
select *, rn=row_number() over (partition by [year], [Month]  order by calendar_date desc ) 
from T8 
where  -- [WeekDay] = 2 and  -- comment this out
        datediff(month, @current_date, Calendar_date)=-1
)
select  * from Calendar 
where rn = 1;

Of course, there are way more efficient way to calculate the last day of last month, such as the following code (but my intention is to provide a generic pattern using calendar table)

declare @current_date datetime = '2022-01-27' -- getdate();
select dateadd(day, -1, dateadd(month, datediff(month, 0, @current_date), 0))

3. Find the # of weekend days between two dates

-- find the # of weekend days between two dates
go
declare @from datetime = '2021-12-23'
, @to datetime = '2022-01-29'; 
; with t(n) as (
select 1 as n union all select 1 as n)
, t2(n) as ( select 1 from t cross join  t as tn )
, t3(n) as ( select 1 from t2 cross join  t2 as tn )
, t4(n) as ( select 1 from t3 cross join  t3  as tn)
, t5(n) as ( select 1 from t4 cross join  t4  as tn)
, t6(n) as ( select 1 from t5 cross join  t5  as tn)
, t7(n) as (select n=row_number() over (order by n) from t6)
, t8 as (select [year] =  datepart(yy, @from+n-1)
, [Quarter] = datepart(qq, @from+n-1)
, [Month] = datepart(mm, @from+n-1)
, [DayOfYear] = datepart(dy, @from+n-1)
, [Day] = datepart(dd, @from+n-1)
, [Week] = datepart(ww, @from+n-1)
-- WeekDay: 1=Mon, Tue=2,...Sat=6, Sun= 7
-- not affected by @@datefirst settings
, [WeekDay] =case (datepart(dw, @from+n-1) +@@datefirst-1)%7 
when 0 then 7 
else (datepart(dw, @from+n-1) +@@datefirst-1)%7 
 end
, [iso_week] = datepart(isoww, @from+n-1)
, [Calendar_date] = @from + n -1
  from t7 where n <= datediff(day, @from, @to)+1)
, Calendar as (
select * from T8 
where [weekday] in (6, 7)  
)
select  [# of Weekend Days] = count(*) from Calendar 
where calendar_date between @from and @to
go

4. Find the first and last working day of the current month

-- find the first / last working day of this month
declare @current_date datetime = '2022-01-27'; -- getdate()
declare @from datetime = dateadd(year, -10, @current_date)
, @to datetime = dateadd(year, 10, @current_date);
; with t(n) as (
select 1 as n union all select 1 as n)
, t2(n) as ( select 1 from t cross join  t as tn )
, t3(n) as ( select 1 from t2 cross join  t2 as tn )
, t4(n) as ( select 1 from t3 cross join  t3  as tn)
, t5(n) as ( select 1 from t4 cross join  t4  as tn)
, t6(n) as ( select 1 from t5 cross join  t5  as tn)
, t7(n) as (select n=row_number() over (order by n) from t6)
, t8 as (select [year] =  datepart(yy, @from+n-1)
, [Quarter] = datepart(qq, @from+n-1)
, [Month] = datepart(mm, @from+n-1)
, [DayOfYear] = datepart(dy, @from+n-1)
, [Day] = datepart(dd, @from+n-1)
, [Week] = datepart(ww, @from+n-1)
-- WeekDay: 1=Mon, Tue=2,...Sat=6, Sun= 7
-- not affected by @@datefirst settings
, [WeekDay] =case (datepart(dw, @from+n-1) +@@datefirst-1)%7 
when 0 then 7 
else (datepart(dw, @from+n-1) +@@datefirst-1)%7 
 end
, [iso_week] = datepart(isoww, @from+n-1)
, [Calendar_date] = @from + n -1
  from t7 where n <= datediff(day, @from, @to)+1)
, Calendar as (
select *, rn=row_number() over (partition by [year], [Month]  order by calendar_date asc ) 
from T8 
where [weekday] not in (6, 7) and 
datediff(month, @current_date, Calendar_date)=0
)
select  * from Calendar 
where rn = 1 or rn = (select max(rn) from Calendar);
go

5. Find the 5th Wednesday of each quarter of 2022

-- find the the 5th Wednesday of each quarter in 2022
declare @current_date datetime =  getdate() -- today is '2022-01-27'
declare @from datetime = dateadd(year, -10, @current_date)
, @to datetime = dateadd(year, 10, @current_date);
; with t(n) as (
select 1 as n union all select 1 as n)
, t2(n) as ( select 1 from t cross join  t as tn )
, t3(n) as ( select 1 from t2 cross join  t2 as tn )
, t4(n) as ( select 1 from t3 cross join  t3  as tn)
, t5(n) as ( select 1 from t4 cross join  t4  as tn)
, t6(n) as ( select 1 from t5 cross join  t5  as tn)
, t7(n) as (select n=row_number() over (order by n) from t6)
, t8 as (select [year] =  datepart(yy, @from+n-1)
, [Quarter] = datepart(qq, @from+n-1)
, [Month] = datepart(mm, @from+n-1)
, [DayOfYear] = datepart(dy, @from+n-1)
, [Day] = datepart(dd, @from+n-1)
, [Week] = datepart(ww, @from+n-1)
-- WeekDay: 1=Mon, Tue=2,...Sat=6, Sun= 7
-- not affected by @@datefirst settings
, [WeekDay] =case (datepart(dw, @from+n-1) +@@datefirst-1)%7 
when 0 then 7 
else (datepart(dw, @from+n-1) +@@datefirst-1)%7 
 end
, [iso_week] = datepart(isoww, @from+n-1)
, [Calendar_date] = @from + n -1
  from t7 where n <= datediff(day, @from, @to)+1)
, Calendar as (
select *, rn=row_number() over (partition by [year], [quarter]  order by calendar_date asc ) 
from T8 
where [weekday] = 3 and 
datediff(year, '2022-01-01', Calendar_date)=0
)
select  * from Calendar 
where rn = 5 -- the fifth record of each quarter of 2022;