Datediff() function

Question

Datediff() function

RKaram

SSCommitted

Points: 1518
More actions
January 24, 2014 at 3:16 am

#288808

What is the difference between
DATEDIFF(W,Date1,Date2)
and
DATEDIFF(D,Date1,Date2)
for example the below code return same results
select DATEDIFF(W,Getdate(),Getdate()+2)
select DATEDIFF(D,Getdate(),Getdate()+2)
Thank You in advance
Rabih
rkaram

Viewing 5 posts - 1 through 4 (of 4 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Although not mentioned in the documentation, W stands for WeekDay. You can see this if you hover with your mouse over the W.

I guess that's probably the same as Day?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

RKaram SSCommitted Points: 1518 More actions · Answer 2

as I understand,

"To calculate the number of days between date1 and date2, you can use either Day of year ("Y") or Day ("D"). When interval is Weekday ("W"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1."

but here the result isnt the case

rkaram

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

It seems to me weekdays calculates days, not the number of Mondays.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

rabih_karam (1/24/2014)
as I understand,
"To calculate the number of days between date1 and date2, you can use either Day of year ("Y") or Day ("D"). When interval is Weekday ("W"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1."
but here the result isnt the case

That's from Access help. The SQL Server function is slightly different. Natch.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden