Convert + Datetime = Headache
-
I'm having a problem using CONVERT on a varchar field that has some "dates" in it. My basic query looks like this:
SELECT txtDate
FROM Table
WHERE ISDATE( txtDate ) = 1
AND CONVERT( DATETIME, txtDate ) BETWEEN
'01/01/2003 00:00:00' AND
'04/30/2005 23:59:59'
When txtDate is a "date", it's in MM/DD/YYYY format. If I take out the CONVERT part of the statement, I get the 4 records that are "dates". If I leave it in, I get an error (converting datetime from character string).
What am I overlooking that is making this not work? I've tried replacing the CONVERT with a CAST (txtDate AS DATETIME) but I still get the same error. Is it something with the strings I'm using in the BETWEEN clause? I can CONVERT and CAST in the SELECT part of the statement without any error (as long as I take the CONVERT or CAST out of the WHERE clause). I've tried using CAST and CONVERT on those strings to get the error to go away, but to no avail.
Thanks.
--Matt
-
It's because the query optimizer is evaluating the between operand first. Try this :
Select dtValidDates.txtDate from
(SELECT txtDate
FROM Table
WHERE ISDATE( txtDate ) = 1) dtValidDates
AND dtValidDates.txtDates between '01/01/2003 00:00:00' AND
'04/30/2005 23:59:59'
-
Ah! Now we're getting somewhere.
Massaging your SQL into:
Select dtValidDates.txtDate from
(SELECT txtDate
FROM Table
WHERE ISDATE( txtDate ) = 1) dtValidDates
WHERE dtValidDates.txtDate BETWEEN '01/01/2003 00:00:00' AND
'04/30/2005 23:59:59'
gives me 1 of the 4 records, because the BETWEEN is doing (I think) string comparison, instead of date comparison. Trying to do CASTs and CONVERTs in the BETWEEN reintroduce me to my friend the syntax error message.
Is there a way to force precedence in the where clause?
-
Can't you run this??
Select dtValidDates.txtDate from
(SELECT txtDate
FROM Table
WHERE ISDATE(txtDate) = 1) dtValidDates
WHERE CONVERT(DATETIME,dtValidDates.txtDate) BETWEEN '01/01/2003 00:00:00' AND
'04/30/2005 23:59:59'
-
SELECT txtDate
FROM Table
WHERE
(case when ISDATE( txtDate ) = 1
then case when CONVERT( DATETIME, txtDate ) BETWEEN '01/01/2003' AND '04/30/2005 23:59:59' then 1 else 0 end
else 0 end ) = 1
* Noel -
When I try to run that, I get the same error (syntax error converting datetime from character string), yes.
The crappy workaround I've developed is:
SELECT txtDate
FROM Table
WHERE
CASE
WHEN ISDATE( txtDate ) = 1
THEN CAST( txtDate AS DATETIME )
ELSE DATEADD(year, 10, getdate())
END BETWEEN '01/01/2003 00:00:00' AND '04/25/2005 23:59:59'
This works because the second date is not allowed to be in the future, so the ELSE date should never be between the two criteria dates.
Thanks for the help!
--Matt
-
Thanks, Noel, that nested CASE does exactly what I want, minus the cheese factor of my earlier solution.
--Matt
-
Can someone explain why the first query I posted didn't work in this context?? I can't reproduce this behavior on my server.
-
Remi,
For some reason I can't find the link now, [ I'll keep looking
]The problem is that (depending on indexes and data) SQL Server optimizer sometimes tries to perform the where clause OUTSIDE of the parenthesis therefore performing something is not supposed to do
I have come across this one more than once!The "Case" is mandatory workflow
hth
* Noel -
Ya... so much control and I didn't even realize it :-).
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply