Datediff usage

Question

Datediff usage

Don Bernstein

Old Hand

Points: 393
More actions
February 8, 2008 at 12:09 pm

#203167

Yes, I like the script, but my favorite part is using Datediff to strip off the time component of a datetime. I just do not get why the DateAdd is needed:
SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0).
Datediff alone seems to work fine. This code supports that assertion:
Declare @D1 as SmallDateTime
Set @D1 = DATEDIFF(dd,0,'1/1/2008 00:00'); Print 'Datetime = ' + Cast(@D1 as VarChar(24))
Set @D1 = DATEDIFF(dd,0,'1/1/2008 6 am'); Print 'Datetime = ' + Cast(@D1 as VarChar(24))
Set @D1 = DATEDIFF(dd,0,'1/1/2008 12:00'); Print 'Datetime = ' + Cast(@D1 as VarChar(24))
Set @D1 = DATEDIFF(dd,0,'1/1/2008 6 pm'); Print 'Datetime = ' + Cast(@D1 as VarChar(24))
Set @D1 = DATEDIFF(dd,0,'1/1/2008 23:59'); Print 'Datetime = ' + Cast(@D1 as VarChar(24))

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 1

I don't think I wrote an article on "DateDiff Usage"... so it would be interesting to find out how my name got on this...

Anyway, nice trick you've found. I tried it a couple of different ways and it seems to work just fine. Not sure I'd trust it in the future, though, most because it shouldn't work that way... MS could fix the "feature" at any time...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Don Bernstein Old Hand Points: 393 More actions · Answer 2

Jeff,

I was reading your article on calculating a number of workdays between dates. Your code was "SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)".

I've seen plenty of suggestions on how to strip off the time component of a date-time field, but this is the most direct.

I was simply suggesting that the DATEADD is superfluous, unless you think that SqlServer may some day refuse to convert the Datediff result to a datetime field. In my clone of your function, I used the short form:

SELECT @StartDate =DATEDIFF(dd,0,@StartDate).

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I think the issue would be whether the implicit CAST will continue to be supported, or if the date values will continue to be stored as floating points where the integer portion is in days.

I'd be more worried about #1 than #2, but then again - 2008 is making some changes to how dates and times are stored, so I'd be careful either way.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 4

Don Bernstein (2/11/2008)
unless you think that SqlServer may some day refuse to convert the Datediff result to a datetime field

That's kind of what I'm thinking... doesn't take much extra time to leave both DATEDIFF and DATEADD in...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Both methods are using an implicit cast of an integer to a datetime.

If the implicit cast of the integer to datetime stops working, then this will also stop working:

select DATEADD(dd,DATEDIFF(dd,0,Mydate),0)

It would need to be changed to this to remove the implicit cast:

select DATEADD(dd,DATEDIFF(dd,0,Mydate),'19000101')

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

Wouldn't you have to write this:

select DATEADD(dd,DATEDIFF(dd,0,Mydate),'19000101')

like this:

select DATEADD(dd,DATEDIFF(dd,'19000101',MyDate),'19000101')

if you wanted to eliminate the implicit conversion between int and datetime?

😎