June 4, 2009 at 9:44 am
In Sql 2000 this type of statement worked as expected:
Select *
FROM {table}
where EnterDate = '02/12/2009'
but it doesn't work under sql2008 (returns no records).
This works under sql 2008:
Select *
FROM {table}
where cast(EnterDate as date) = '02/12/2009'
Question: we have a LOT of code using the first form of Select. Other than setting the database to sql 2005 compatibility mode, what can be done to make the first Select work as expected under sql 2008.
TIA,
barkingdog
June 5, 2009 at 7:21 pm
I don't even have 2008 yet, but am trying to get ready for when we get it. We have many datetime fields currently used for just a date, letting the time portion default to 00:00:00, so I'm very interested in issues like this. Please forgive me if these questions aren't right on the mark:
-- Is EnterDate a datetime?
-- Could this be due to a DATEFORMAT issue, where perhaps your database was set up with mdy and the string is intended to be dmy (or vice versa)? This could happen, I suppose, if your new 2008 db were set to American English and the old one was set to British English (or vice versa).
June 6, 2009 at 4:55 am
I tried a few things. However, I couldn't find any direct solution yet.
Almost everything that can be done for basic date time manipulation (SQL 2008) is mentioned in the following msdn reference article.
http://msdn.microsoft.com/en-us/library/ms180878.aspx
You should be able to get the same info from BOL as well.
I couldnt find a direct solution. Two approaches/tweaks that might work are
1.
DECLARE @myTable TABLE(SampleDateValue DATETIME)
INSERT INTO @myTable VALUES(GETUTCDATE()),(GETUTCDATE()-5),(GETUTCDATE() - 10)
SELECT SampleDateValue FROM @myTable
WHERE CAST(SampleDateValue as DATE) = 'datePart'
--Note that this approach is already mentioned in the initial thread post.
2.
DECLARE @myTable TABLE(SampleDateValue DATETIME)
INSERT INTO @myTable VALUES(GETUTCDATE()),(GETUTCDATE()-5),(GETUTCDATE() - 10)
SELECT SampleDateValue FROM @myTable
WHERE CONVERT(VARCHAR(10),SampleDateValue,111) = 'datePart'
Both the solutions require code changes.
June 6, 2009 at 5:45 am
Do the values in Enterdate have any times associated? If so, that query won't work as it will only match dates that have midnight as their time. If that is the case, rather do the query like this. The case on the column prevents index seeks.
Select *
FROM
where EnterDate >= '02/12/2009' AND EnterDate < '03/12/2009'
So, we're doing a range search for any date that falls in that day. Oh, and I recommend using the universal date format instead. The one you're using there is ambiguous.
So use '2009/12/02' - yyyy/mm/dd. If you put it the other way around, there's no way to tell if it's in mm/dd/yyyy or dd/mm/yyyy
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply