June 29, 2011 at 8:48 am
The enclosed SQL syntax works for most time, but causes a data error in some instances.
First routine rounds time to nearest minute. With some dates it sometime will have a 3 millisecond error
Second routine removes date portion of DateTime and changes it to 1900-01-01. With some dates it sometime will have a 3 millisecond error also.
CAN ANYONE HELP?
DECLARE @EndDateREF DateTime
DECLARE @EndDateRounded DateTime
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
set @EndDateREF = '6/27/2011 2:30:02 PM'
set @EndDateRounded = dateadd(day, 0, datediff(day, 0, @EndDateREF))
+ cast(round(cast(cast(convert(varchar(12), @EndDateREF, 14) as datetime) as float) * 1440.0 / 1, 0) / (1440.0 / 1) as datetime)
select @EndDateREF as BaseTime,@EndDateRounded as RoundedTime
declare @EventTime datetime
set @EventTime = '6/27/2011 10:00:00 PM'
select CAST( @EventTime AS FLOAT )
select FLOOR( CAST( @EventTime AS FLOAT ))
select CAST((CAST( @EventTime AS FLOAT ) - FLOOR( CAST( @EventTime AS FLOAT )))AS DATETIME)
June 29, 2011 at 9:52 am
Rounding is an intrinsic property of the DATETIME data type and FLOAT is an approximate data type so no guarantees. See "Accuracy" in this article: http://technet.microsoft.com/en-us/library/ms187819.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 10:29 am
Thanks for the response.
It looks like I can get rid of the issue by using smalldatetime instead of datetime.
Regards
Michael Hilligas
June 29, 2011 at 10:31 am
Cool, happy you got it sorted 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply