datetime convertion in SqlServer 2000

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