datetime comparision - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

datetime comparision

Author

Topic

Bool
Starting Member

4 Posts

Posted - 2007-05-22 : 02:42:33

I have a select query as following.

select t_datetime from t_table where t_datetime >= '20070521 10:20:49.001'

t_datetime
-----------
2007-05-21 10:20:49.000

The millisecond part is being ignored in comparision probably because of implicit converstion of string to a datetime format that does not cover the millisecond part. So now how do I do a proper datetime comparision in sql server for the above example.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-22 : 02:50:18

This is happening so because SQL Server stores datetime data to the accuracy of 3.33 miliseconds.

From BOL:

quote:
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Bool
Starting Member

4 Posts

Posted - 2007-05-22 : 03:05:19

But my question is different. I wanted to know, how to get correct results out of the query I posted. Obviously '20070521 10:20:49.001' is greater than '20070521 10:20:49.000' and should not have come in the results.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-22 : 03:12:48

Excute this and check for yourself:

if cast('20070521 10:20:49.001' as datetime) > cast('20070521 10:20:49.000' as datetime) 
print 'Not Equal'
else
print 'Equal'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Bool
Starting Member

4 Posts

Posted - 2007-05-22 : 03:25:08

No. This is not working. I just executed this.

if cast('20070521 10:20:49.001' as datetime) > cast('20070521 10:20:49.000' as datetime)
print 'Not Equal'
else
print 'Equal'

Equal

Infact

select cast('20070521 10:20:49.001' as datetime)

is returning '2007-05-21 10:20:49.000'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-22 : 03:36:55

Yes. That's my whole point.
SQL Server considers both equal because of miliseconds accuracy limitation.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 04:12:56

[code]declare @date datetime
select @date='20070521 10:20:49.001'
select @date
select @date='20070521 10:20:49.002'
select @date
select @date='20070521 10:20:49.003'
select @date
select @date='20070521 10:20:49.004'
select @date
[/code]

Madhivanan

Failing to plan is Planning to fail

Bool
Starting Member

4 Posts

Posted - 2007-05-22 : 04:44:56

OK thanks, I understood it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources