SQL Server Date Time Calculation Examples

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