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

-- populate the table D_Date, 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

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