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 |
|
gregsenne
Starting Member
7 Posts |
 Posted - 2009-08-17 : 16:08:14
|
| Ok, this is very frustrating. When comparing date values, my seconds round up (<30 goes to 0, >29 goes to the next minute)For example, I run this query, which should obviously not return any rows, does return rows.SELECT * FROM TASKS WHERE CONVERT(SMALLDATETIME,'8/9/2009 11:59:59 PM') BETWEEN CONVERT(SMALLDATETIME,'8/10/2009 12:00:00 AM') AND CONVERT(SMALLDATETIME,'4/10/2010 11:59:59 PM')But if I change the time to 11:59:29 PM in the first convert statement, it won't return any rows.WHY!!!!!?????????? |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-08-17 : 16:13:33
|
Thats because you have used SMALLDATETIME data type. Use DATETIME...it will keep the time until the milliseconds..FROM BOL--Returns time as 12:35.SELECT CAST('2003-05-08 12:35:29.998' AS smalldatetime);GO--Returns time as 12:36.SELECT CAST('2003-05-08 12:35:29.999' AS smalldatetime);GO |
 |
|
|
|
|
|
Why does my seconds round up?