Calculate Thanksgiving Date with T-SQL


In the US, Thanksgiving is always celebrated on the fourth Thursday of November.
For any given date, we can find out Thanksgiving day in that year with T-SQL.

 


declare @anydate datetime='2014-01-30'
declare @dt datetime='20151101'
Select  @dt=dateadd(month,10,dateadd(year, datediff(year,0,@anydate),0)) ---November 1st


select  DateAdd(day, (7+5 -DatePart(weekday, @dt))%7, @dt) firstThuIntheMonth
select  DATEADD(WEEK, 3, DATEADD(d, Case when DATEPART(dw, @dt)>5 Then 7 Else 0 ENd + (5 - DATEPART(dw, @dt)), @dt)) Thanksgivingday
Select  DATEADD(WEEK, 3, DateAdd(day, (7+5 -DatePart(weekday, @dt))%7, @dt) ) Thanksgivingday

/*
Year	U.S. Thanksgiving	
2012	Thursday, November 22	
2013	Thursday, November 28	
2014	Thursday, November 27	
2015	Thursday, November 26	
2016	Thursday, November 24	
2017	Thursday, November 23	
2018	Thursday, November 22	
2019	Thursday, November 28	
*/


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s