SqlServer 2000 with SP3. RepTime is a datetime in TableA. I ran the two commands beblow:
SELECT distinct CAST([RepTime] AS INT) FROM TableA
39004
39002
39003
select max(RepTime),min(RepTime) from TableA
2006-10-16 10:36:03.940 2006-10-13 17:32:00.080
From 2006-10-13 to 2006-10-16, there are four days. But I got three distinct int from it. Anyone knows ?
Maybe you have two entries having the same date but different times?
DECLARE @.test AS TABLE (dat datetime);
INSERT into @.test values ('2006-12-19')
INSERT into @.test values ('2006-12-20')
INSERT into @.test values (GETDATE())
-- This retrieves 3 columns
SELECT DISTINCT dat FROM @.test
-- This retrieves 2 columns
SELECT DISTINCT CAST(dat AS INT) FROM @.test
That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.
|||Very interesting while dig in to your issue..
SQL Server Converts the date using the following logic...
Declare @.Date as Datetime
Select @.Date = '2006-10-16 10:00:00'
select Cast(@.Date as Int) -- Result : 39004
Select @.Date = '2006-10-16 11:59:59'
select Cast(@.Date as Int) -- Result : 39004
Select @.Date = '2006-10-16 12:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)
Select @.Date = '2006-10-16 23:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)
Select @.Date = '2006-10-17 10:00:00'
select Cast(@.Date as Int) -- Result : 39005
Understood, the Integer cast will take one day from 12:00:00 PM to 11:59:59 AM (Strange Buddy = Bcs, after 12AM the value will be >= 39004.5 when the float number converted to integer it will round off the value...)
To overcome this issue use the following query..
SELECT distinct CAST(Convert(Datetime,Convert(Varchar,[RepTime],101)) AS INT) FROM TableA
Here we are omiting the Time field completly..
|||
Lucky P wrote:
That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.
Nope... Time is big concern here Lucky P
|||You're completely right...
When converting to a Decimal Value, the Date '2006-10-16 12:00:00' is retrieved as 39004.5, which is rounded up to 39005 when converting it to INT.....
I made the mistake because im running the query before 12:00
|||
Yes absolutly it is round off issue... ..
So we can overcome this issue by the following query ..
SELECT distinct Cast(Round(CAST([RepTime] AS Float),0,2) as INT) FROM TableA
|||I got it. I made the same mistake too. Thanks to you all!|||I use this one now:
SELECT distinct floor(cast([RepTime] as float)) FROM TableA
No comments:
Post a Comment