January 21, 2011 at 9:10 am
I am having to do a query on a date range where the date is defined as a varchar in the table. This is very slow when I have to set the date with a datetime variable.
MyDate is a varchar(30) in the table
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '01/20/2011'
SET @EndDate = '01/20/2011 23:59:59'
--This query is very slow and take minutes to retrieve the data
SELECT *
FROM Table
WHERE MyDate Between @StartDate and @EndDate
--When I hard code the dates this comes back almost instantly
SELECT *
FROM Table
WHERE MyDate Between '01/20/2011'and '01/20/2011 23:59:59'
I would like to know what SQL is doing exactly when variables are declared for the dates and any way around this issue. Also, are there any best practices when dealing dates that are declared as varchars.
Thanks
Tim
January 21, 2011 at 9:30 am
It's most probably because of implicit conversion.
All your data will be converted internally to datetime when using the variables, since the datetime datatype has a higher precedence.
The two queries cannot be compared directly since the first one checks for date values between startdate and enddate whereas the second one will only compare string values (with all the side effects involved...).
Speaking of it: Why is this column a varchar in the first place and why is it defined with a length of 30? And even more important: why is it stored in a format that can fail depending on the DATEFORMAT or LANGUAGE setting?
You might run into unpredictable issues sooner or later.
Consider this:
WHERE MyDate Between '01/19/2011'and '01/20/2011 23:59:59'
This will also return '01/20/2010 13:00:00'...
Therefore, I strongly recommend to store date values as datetime or smalldatetime. If you insist in storing it as varchar, at least pick the correct length and use a format that will return the expected data.
January 21, 2011 at 9:45 am
I am inheriting this data but I think the values that are beginning inserted into the table are not always guaranteed to be a date and I can't not change the data because it is coming from a customer and we don't want to manipulate their data. I know can come up with something else which will take some development time but I would like to solve this issue without any more development.
Tim
January 21, 2011 at 9:52 am
In that case I would declare the variables as varchar instead of datetime.
January 21, 2011 at 9:55 am
I tried declaring the dates as varchars and the query returned records outside of the date range.
January 21, 2011 at 10:09 am
It depends on what you define as the range:
If you want to keep the column data type but compare dates, you have to use the proper data type leading to an implicit conversion and suffering performance.
If you want to have correct results in a reasonable time and keep the data type, you'd need to store it in a format that will sort "correctly" (= equivalent to the date format). Like YYYY-MM-DD HH:MM:SS. But be aware that you might get non-date values as well unless there is a check constraint...
If you want to have fast results and correct data, change the data type of the column or add another computed persisted column with the datetime data type.
January 21, 2011 at 1:49 pm
I got it to work using dynamic sql and converting the entire select statement to a string and executing the string.
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '01/20/2011'
SET @EndDate = '01/20/2011 23:59:59'
DECLARE @Script nvarchar(5000)
--This query is very slow and take minutes to retrieve the data
SET @Script = 'SELECT *
FROM Table
WHERE MyDate Between ''' + @StartDate + ''' and ''' + @EndDate + ''''
execute sp_executesql @Script
I know not ideal but hopefully it is something I can change in the future.
Tim
January 21, 2011 at 2:00 pm
This will still get you incorrect results. You're avoiding the conversion this way, which is then avoiding finding the correct values.
Let me show you what's happening.
You have 5 dates:
DECLARE @Datevalue TABLE ( dtCol DATETIME, chrCol VARCHAR(30))
INSERT INTO @Datevalue VALUES ( '1/10/2008', '1/10/2008')
INSERT INTO @Datevalue VALUES ( '1/15/2010', '1/15/2010')
INSERT INTO @Datevalue VALUES ( '2/1/2009', '2/1/2009')
INSERT INTO @Datevalue VALUES ( '4/5/2004', '4/5/2004')
INSERT INTO @Datevalue VALUES ( '04/5/2004', '04/05/2004') -- Note the 04/05 vs. 4/5
INSERT INTO @Datevalue VALUES ( '8/3/2010', '8/3/2010')
SELECT * FROM @DateValue
SELECT * FROM @DateValue WHERE dtCol between '1/1/2010' and '6/1/2010' ORDER BY dtCol
SELECT * FROM @DateValue WHERE chrCol between '1/1/2010' and '6/1/2010' ORDER BY chrCol
SELECT * FROM @DateValue WHERE chrCol between '01/1/2010' and '6/1/2010' ORDER BY chrCol
If they're not in the system as DATETIME, and they're not formatted as was already described above (year first, then 2digit month, then 2 digit day) then you can't varchar range find these properly.
I understand your dilemma, but you might be better off adding a secondary column that does the datetime conversion for you for all valid values, default anything that doesn't have a valid value, and work from that column.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 21, 2011 at 2:18 pm
I see the issue. It looks like I need to come up something sooner that later.
Thanks Tim
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply