Last Mon in May and Last Thursday in Nov

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Last Mon in May and Last Thursday in Nov

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-17 : 17:03:42

Hi,

I need to get the last Monday in the month of May and the last Thursday in the month of Nov.

Thanks,
Petronas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-17 : 17:20:36

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

SELECT MAX(date) FROM f_table_date('20090501', '20090530') AS f where weekday = 'monday' -- last monday
SELECT MAX(date) FROM f_table_date('20091101', '20091130') AS f where weekday = 'thursday' -- last thursday

E 12°55'05.63"
N 56°04'39.26"

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-17 : 22:48:06

Thanks Peso,

I need to incorporate the last Monday in May and Last Thursday in Nov for the Holidays in the Calendar table. Rt.now in the query if it is a Saturday or Sunday then Business Day =0, likewise I need to display 0 for the last Monday in May(Memorial day) and the last Thursday in November(Thanksgiving day). I am populating the Calendar table from 1990 to 2020..

Here is my code..
CREATE TABLE #Calendar(Calendar_Date datetime, Year_Num smallint,
Quarter_Num smallint,Day_Name varchar(15),Month_Num smallint,Business_Day bit,Week_Num smallint)

DECLARE @dtDate datetime
SET @dtDate = '1/1/1990'
WHILE (@dtDate <= '12/31/2020')
BEGIN
INSERT INTO #Calendar
(Calendar_Date,Year_Num,Quarter_Num,Day_Name,Month_Num,Business_day,Week_Num )

SELECT @dtdate,
Year(@dtDate), datepart (qq,@dtdate),
datename(dw,@dtdate),datepart(month,@dtdate),
CASE WHEN DATENAME(dw,@dtdate) NOT IN ('Sunday','Saturday') THEN 1 else 0 end,
datepart(wk,@dtdate)
SET @dtDate = DATEADD(dd,1,@dtDate)
END

SELECT * FROM #Calendar
order by 1

Thanks for your help,
Petronas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources