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	


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s