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.
| Author |
Topic |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
 Posted - 2008-06-04 : 08:37:16
|
| am getting some unexpected behaviour using datetimes (on SP2, though i haven't tried other builds), as below...<code>-------------------------------------------------------------------------if i run...select dateadd(day,datediff(day,0,getutcdate()),0) as today ,dateadd(ms,-1,dateadd(day,datediff(day,0,getutcdate()),1)) as end_of_today/*i'd expect the result to be...today end_of_today----------------------- -----------------------2008-06-04 00:00:00.000 2008-06-04 23:59:59.999what i actually get is...today end_of_today----------------------- -----------------------2008-06-04 00:00:00.000 2008-06-05 00:00:00.000*/--------------------------------------------------------------------------i run...select dateadd(day,datediff(day,0,getutcdate()),0) as today ,dateadd(ms,-2,dateadd(day,datediff(day,0,getutcdate()),1)) as end_of_today/*i expect...today end_of_today----------------------- -----------------------2008-06-04 00:00:00.000 2008-06-04 23:59:59.998i get...today end_of_today----------------------- -----------------------2008-06-04 00:00:00.000 2008-06-04 23:59:59.997*/--------------------------------------------------------------------------even as simple as this, the result is the same...select cast('23:59:59.999' as datetime) as end_of_day/*results in....end_of_day-----------------------1900-01-02 00:00:00.000*/</code>can anyone shed any light? and also suggest how to reliably create a datetime like yyyymmdd hh:mi:59.999 ?Em |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-06-04 : 09:01:43
|
| Datetime precision is only accurate to about 3 milliseconds. For date range comparisons it is easiest to say:dtCol >= '2008-06-04 00:00:00.000' and dtCol < '2008-06-05 00:00:00.000'that way your criteria covers all times for that day (or those days)Be One with the OptimizerTG |
 |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-06-04 : 09:29:37
|
| unfortunately i can't on this occasion. the application i'm passing these dates to looks for startdate >= datecol1 and endDate <= datecol2 ...so the date 'crossover' becomes pretty crucialso the question remains... how can i be sure to get yyyymmdd hh:mi:59.999 ?Em |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-06-04 : 10:03:30
|
| The best you can do is take 12:00 AM the next day and subtract 3 ms from it. Query your data to see if there are any dates that are > .997 and < .000 (the next day). My guess is there won't be.Be One with the OptimizerTG |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-06-05 : 06:35:04
|
well on running the following...select * from myTable where datepart(ms,TimestampColumn) > 997...i got no rows returned on a 10,000,000 row table (prod will be well over 100mill), so i'm assuming that if i can't write a .999 value then netiher can the timestamp ( just inserted with a getdate() ). so i'll leave it at .997 for now, though it just doesn't feel very thorough to me  Em |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-06-05 : 08:32:19
|
oh come on! A hard-core, tatooed, rock-and-roll, biker-chick lives for DANGER, not thoroughness.  Be One with the OptimizerTG |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-06-05 : 09:43:43
|
lol... i must have missed off obsessive and anal from my list  Em |
|
|
|
|
|
|
|
|
level of precision with date functions