| Author |
Topic |
|
macsterling
Yak Posting Veteran
56 Posts |
 Posted - 2010-04-22 : 16:55:49
|
| QA (me) was asked to find out how many transactions would be selected by a process. I wrote a SQL and came up with a different number of rows than the developer who had written a different SQLThe two two SQL statements that should give the same results are:Statement 1 "where t.auditupdateddate >= '4/20/2010 8:13 PM' Statement 2 "where t.auditupdateddate > '2010-04-20 20:12:59' ----------------------------------------------------------there are two rows that have t.auditupdateddate = '2010-04-20 20:13' They ARE SELECTED by the Statement 1 but NOT by Statement 2This makes no sense. Any thoughts - what is happening?I am afraid that both code formats may be in different places in the system (quite large) and would be very difficult to find a problem.I know that we should be using Format 2 (mine, of course), but the other is already in the code (in production) and can't be changed easily (not up to me) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-22 : 18:55:53
|
quote:
Originally posted by tkizer
Actually statement 1 is correct. Statement 2 isn't correct because it doesn't include the milliseconds
I disagree. Statement 1 returns more records than statement 2. If it were a millisecond issue, then statement 2 would potentially return more records. eg. '2010-04-20 20:12:59.123' would be returned by statement 2, but not statement 1. macsterling, what happens if you use this (Which is actually what you should be using)?where t.auditupdateddate >= '2010-04-20 20:13:00.000'------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-22 : 19:34:38
|
quote:
Originally posted by tkizer
"where t.auditupdateddate >= '2010-04-20 20:13:00.000'" is equivalent to "where t.auditupdateddate >= '4/20/2010 8:13 PM'" The times are just in different formats. 8:13pm is at 0 seconds and 0 milliseconds.
Yes (except for the regional issues with the date, of course), but that doesn't explain why >= '4/20/2010 8:13 PM' would return more rows than > '2010-04-20 20:12:59'. Everything that passes the first test should also pass the second. Unless the OP has them mixed up.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2010-04-22 : 22:17:02
|
| Originally, statement 1 gave us 137 rows and statement 2 gave us 135. I tried the suggestion by DBA IN THE MAKING and we got 137 (the correct answer) - but can someone explain why "> 12:59" returns fewer rows than ">= to 13:00" ? (I knew I liked mainframes better....) |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-23 : 03:46:58
|
quote:
Originally posted by macsterling
Originally, statement 1 gave us 137 rows and statement 2 gave us 135. I tried the suggestion by DBA IN THE MAKING and we got 137 (the correct answer) - but can someone explain why "> 12:59" returns fewer rows than ">= to 13:00" ?
Looks like a SQL but to me. What service pack are you running?quote:
(I knew I liked mainframes better....)
Now now..  :)------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-04-23 : 04:24:54
|
| What is the datatype of auditupdateddate?If it is smalldatetime, you will get different results becuase of rounding to minutesselect cast( '2010-04-20 20:12:59' as smalldatetime)The value becomes 2010-04-20 20:13:00 and you have used > operator which will exclude these recordsMadhivananFailing to plan is Planning to fail |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-23 : 05:13:24
|
quote:
Originally posted by madhivanan
What is the datatype of auditupdateddate?If it is smalldatetime, you will get different results becuase of rounding to minutesselect cast( '2010-04-20 20:12:59' as smalldatetime)The value becomes 2010-04-20 20:13:00 and you have used > operator which will exclude these records
Of course. It's all so simple now that you put it that way.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2010-04-23 : 08:10:35
|
| The db is SQL2000 (!!) Giant Giant company, but what the hay?? SQL was run using SQL Server Management Studio 9.00.1399.00the format is smalldatetime |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-04-23 : 08:30:35
|
quote:
Originally posted by macsterling
The db is SQL2000 (!!) Giant Giant company, but what the hay?? SQL was run using SQL Server Management Studio 9.00.1399.00the format is smalldatetime
As it is smalldatetime beware of minute rounding and use method 1Dont include seconds part that may get rounded and lead to unexpected resultMadhivananFailing to plan is Planning to fail |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
conflict in date formats
MadhivananFailing to plan is Planning to fail