December 28, 2009 at 2:57 pm
I am having difficulty trying to query data and using the where clause below
select *
from sales
where datesvc between '9/1/2009' and '9/30/2009'
The problem is, the result set that I get after running this query, stops at a datesvc of 9/29/2009 instead of 9/30/2009.
I checked the tables to make sure that there are records with a datesvc of 9/30/2009.
When I use between '9/1/2009' and '10/1/2009' then see results for 9/30/2009.
Is there a fix to this date problem? Has anyone else ran into this problem? This is really screwing up my results and reports.
Thanks
:w00t:
December 28, 2009 at 3:10 pm
The query retrieves the expected rows because the date values in the query and the datetime values stored in the RateChangeDate column have been specified without the time part of the date. When the time part is unspecified, it defaults to 12:00 A.M. Note that a row that contains a time part that is after 12:00 A.M. on 1998-0105 would not be returned by this query because it falls outside the range.
http://technet.microsoft.com/en-us/library/ms187922.aspx
Is this the case in ur example? is time is after 12 AM?
December 28, 2009 at 3:21 pm
Typically you would do this:
select *
from sales
where datesvc >= '9/1/2009'
and datesvc < '10/1/2009'
December 28, 2009 at 8:05 pm
Steve Jones - Editor (12/28/2009)
Typically you would do this:
select *from sales
where datesvc >= '9/1/2009'
and datesvc < '10/1/2009'
To expand on what Steve is showing here, you need to understand what those dates are being converted to. When specifying them this way - they are being implicitly converted from the string representation to a datetime. So, the above is equivalent to:
select *
from sales
where datesvc >= '2009-09-01 00:00:00.000'
and datesvc < '2009-10-01 00:00:00.000'
By using a range check instead of between - we can make sure we get all datetime values up to the last millisecond before the next day. Using between, you would have to specify the end time - which on 2005 would be 23:59:59.997. However, if you do that - when you upgrade to 2008 your queries could end up missing data if the columns are then modified to the new datetime format which has a higher precision.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply