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 |
|
deanheathen
Starting Member
4 Posts |
 Posted - 2006-11-11 : 11:52:52
|
| I've run into a problem with some queries I'm running where I get records returned that shouldn't be:SELECT * FROM tblDataTable WHERE EventDateTime BETWEEN '2006-01-01 05:00:00' AND '2006-01-02 04:59:00'Now the problem isn't that I'm not getting the records I need. I am getting those, but I'm getting extra records that don't fit this query:Example: EventDateTime = '2006-01-02 05:55:00'would be returned under this query, but as '2006-01-02 00:55:00' ***Notice that the decimal portion on the date is slightly changed, the hour was 05 but is now 00which DOES fit the query. THe problem is that when I use query manager to return ALL records in the table, the data in that field is correct. It says the hour is 05 which it should be. Why would this data be incorrectly read then selected? I'm using ADO and SQL 2000 (trial). If anyone has any Ideas I'd be most appreciative.Dean |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-11 : 12:34:41
|
you probably have a row with the value of '2006-01-02 00:55:00' because what you think is happening can't happen. i hope...  Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
deanheathen
Starting Member
4 Posts |
Posted - 2006-11-11 : 12:40:41
|
| I wish that were true, but it isn't. the Record defintely exists as '2006-01-02 05:55:00'. I know this because the rest of the data in that record is correct. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-11 : 12:44:16
|
| what dateformat do you use? mdy or dmy?run set dateformat MDY or set dateformat DMYdepending on which you're using and try again.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-11 : 13:10:25
|
| Or use ISO format as 20060102 (which is January 2, 2006).Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
DateTime queries return extra records