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)
Setting time to zero in a datetime object
Tyrael Tong
Thanks for picking me up, Mani; I had forgotten about this snag.
chadhowell
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
j2associates
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)
Jeff Sholl
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.