Use of DATEADD to obtain midnight of the previous day 2

[unclejimbob]

I would like to find midnight of the previous day.

Try the following code:

[tt]
DECLARE @start_date DATETIME
DECLARE @start_date1 DATETIME
DECLARE @start_date2 DATETIME

SET @start_date = getdate()

PRINT cast(@start_date as varchar(50))

SET @start_date1 = DATEDIFF(dd,0,@start_date)

PRINT cast(@start_date1 as varchar(50))

SET @start_date2 = DATEADD(day,DATEDIFF(dd,0,@start_date),0)

PRINT cast(@start_date2 as varchar(50))

[/tt]

Question: why do I need to bother with this...

DATEADD(day,DATEDIFF(dd,0,@start_date),0)

when this...

DATEDIFF(dd,0,@start_date)

returns the same result ?

Is it because without the use of DATEADD I am doing some sort of implicit data conversion ? I've been using the DATEADD(day,DATEDIFF(dd,0,@start_date),0) version for ages but this morning I woke up and thought 'Why am I doing this ?' and here we are.

When replying please remember to use small words and write slowly - so I can follow along ;-)

 

[gmmastros]

Actually, it's kinda simple. DateAdd returns a DateTime data type whereas DateDiff returns an integer.

Sometimes that's ok, but other times it's not. There are 2 types on data conversions that can occur. You can explicitly convert data types (by using cast or convert). The other type of data conversion is implicit. This happens when SQL server automagically converts the data type for you.

To see what I mean, run this code:

declare @iTemp Integer
Declare @dteTemp DateTime

Set @iTemp = 30000
Set @dteTemp = @iTemp

Select @iTemp, @dteTemp

Eventhough @dteTemp is DateTime, you can set it's value to an integer. When doing this, you get an implicit data type conversion.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[r937]

but that's just it, they don't return the same result

DATEADD(day,DATEDIFF(dd,0,getdate()),0) = 2007-05-20 00:00:00

DATEDIFF(dd,0,getdate()) = 39220

r937.com | rudy.ca

 

[unclejimbob]

Thanks guys, clear as day now I look at it again. When used in the context of an implicit data type conversion - as I have been doing - the two statements return the same result.