SQL Days until Christmas

Question

SQL Days until Christmas

mfink

SSCommitted

Points: 1632
More actions
October 15, 2010 at 8:44 am

#388014

I was asked to calculate the days until christmas
Select DateDiff(dd,getdate(),'12-25-' +
convert(nvarchar(4),DateName(yyyy,getdate())))
I feel cheesey today 😀

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

nvarchar? String conversions are the nastiest way to work with dates. Also yours will give -ve numbers for the days between christmas and new year

I think this works...

DECLARE @DateToCheck DATETIME

SET @DateToCheck = GETDATE()

SELECT CASE

WHEN DATEDIFF(dd,@DateToCheck,DATEADD(dd,24,DATEADD(mm,11,DATEADD(yy,DATEDIFF(yy,0,@DateToCheck),0)))) < 0

THEN DATEDIFF(dd,@DateToCheck,DATEADD(dd,24,DATEADD(mm,11,DATEADD(yy,DATEDIFF(yy,0,@DateToCheck)+1,0))))

ELSE DATEDIFF(dd,@DateToCheck,DATEADD(dd,24,DATEADD(mm,11,DATEADD(yy,DATEDIFF(yy,0,@DateToCheck),0))))

END AS DaysTilComingChristmas

Edit: Improved readability. Not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

selectDaysTillChristmas =

datediff(dd,getdate(),dateadd(yy,datediff(yy,-1,getdate()),-7))

Results:

DaysTillChristmas

-----------------

71

Note: This will always give you Dec 25 for the current year:

select dateadd(yy,datediff(yy,-1,getdate()),-7)

It's an adaption of the following that gives the last day of the current year:

select dateadd(yy,datediff(yy,-1,getdate()),-1)

drew.allen SSC Guru Points: 76985 More actions · Answer 3

Michael Valentine Jones (10/15/2010)
selectDaysTillChristmas = datediff(dd,getdate(),dateadd(yy,datediff(yy,-1,getdate()),-7))

But this will give you negative numbers from Dec. 26-31. Here is the modified code to always give you the NEXT Christmas.

select DateDiff(Day, GetDate(), dateadd(yy,datediff(yy,-1,DateAdd(Day, 6, GetDate())),-7))

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

GSquared SSC Guru Points: 260824 More actions · Answer 4

Or do the really simple thing and create a calendar table with all your rules built in, and just select counts from that. It's fast, easy, and gets the job done right.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GSquared (10/15/2010)
Or do the really simple thing and create a calendar table with all your rules built in, and just select counts from that. It's fast, easy, and gets the job done right.

Spoilsport. 😉

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

frank 36119 Valued Member Points: 69 More actions · Answer 6

DECLARE @Christmas datetime

select @Christmas = '2016-12-25 00:00:00'

select convert(varchar (2),day(dateadd(d,-day(Getdate()),@Christmas)))+ ' Days, ' +

convert(varchar,24 - datepart(HH, getdate())) +' Hours and ' +

convert(varchar (2),60 - datepart(mi, getdate()))+ ' Minutes til Christmas'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

May have been a lot of typing, but then I built it step by step:

set nocount on;

set statistics time on;

with BaseDate as (

select DATEADD(day,datediff(day,0,getdate()),0) BaseDate

), WorkingDate as (

select

Today = DATEADD(second,datediff(second,BaseDate,getdate()),BaseDate)

from

BaseDate

), UpcomingChristmasDay as (

select

Today,

ChristmasDay = dateadd(yy,datediff(yy,-1,DateAdd(Day, 6, Today)),-7)

from

WorkingDate

), TimeToChristmas as (

select

datediff(second,Today,ChristmasDay) SecondsTilChristmas

from

UpcomingChristmasDay

), CountDownToChristmas as (

select

DaysToChristmas = SecondsTilChristmas / (24 * 60 * 60),

HoursToChristmas = SecondsTilChristmas / (60 * 60) % 24,

MinutesToChristmas = SecondsTilChristmas / 60 % 60,

SecondsToChristmas = SecondsTilChristmas % 60

from

TimeToChristmas

)

select

REPLACE(

REPLACE('$Days$ Days $Hours$ Hours $Minutes$ Minutes $Seconds$ Seconds until Christmas',

'$Days$', CAST(DaysToChristmas as varchar(3))),

'$Hours$', CAST(HoursToChristmas as varchar(2))),

'$Minutes$', CAST(MinutesToChristmas as varchar(2))),

'$Seconds$', CAST(SecondsToChristmas as varchar(2)))

from

CountDownToChristmas

;

set statistics time off;

set nocount off;

go

webburada Grasshopper Points: 17 More actions · Answer 8

webburada

Grasshopper

Points: 17

June 11, 2017 at 4:12 am

#1946322

thanks