# Setting time to zero in a datetime object

### Pregunta

• Anybody know of an easier way to set the timepart to zero in a datetime object than:

declare @day as datetime select @day=getdate()

select @day = dateadd(hh,-datepart(hh,@day),@day)

select @day = dateadd(mi,-datepart(mi,@day),@day)

select @day = dateadd(ss,-datepart(ss,@day),@day)

select @day = dateadd(ms,-datepart(ms,@day),@day)

jueves, 11 de enero de 2007 9:39

### Todas las respuestas

• use the following exp..

Convert the current datetime to mm/dd/yyyy string format then convert the convert back the string to datetime

Select @day = Convert(datetime,Convert(varchar,@day,101),101)

jueves, 11 de enero de 2007 9:45
• Here's another way to do it.  It's faster than the previously mentioned approach.

Select @Day = DateAdd(Day, DateDiff(Day, 0, @Day), 0)

To explain how this works...

First look at: DateDiff(Day, 0, @Day)

You need to realize that the 0 in this statement refers to day 0, which is Jan 1, 1900.  So the DateDiff function returns the numbers of days between Jan 1, 1900 and @Day.  Also, realize that DateDiff always returns an integer (so the time component is removed).

Next, we simply add that number of days to (day 0) to convert this back to a date.

Doing math calculations on the date will be faster than converting back and forth from strings.

jueves, 11 de enero de 2007 14:03
• Warning:

While using integer casting remeber that if your date' time is greater than 12:00 PM then it will return the next day date instead of given date.. use the following logic to avoid this..

select cast(floor(cast(@day, as float)) as datetime)

Example:

Declare @Day as Datetime
Select @Day = '2006-01-11 12:00:00'
Select cast(floor(cast(@Day as float)) as datetime)

--Result : 2006-01-11 00:00:00.000

Select @Day = '2006-01-11 12:00:00'
Select cast(cast(@Day as int) as datetime)

--Result : 2006-01-12 00:00:00.000

jueves, 11 de enero de 2007 14:28