I have a varchar
column in my database table, on the row I would like to return it is populated as '2018-12-26T00:00:00.000
' (quotes mine, not included in actual value). When I try to query for this value whenever it is a valid date, e.g.
SELECT
myValue
FROM
myTable
WHERE
ISDATE(myValue) = 1
it returns properly. However, I need this value to be converted to DATE
. When I try something like this:
SELECT
CAST(myValue AS DATE) AS myValueFormatted
FROM
myTable
WHERE
ISDATE(myValue) = 1
I get an error
Conversion failed when converting date and/or time from character string
Is there any other way I can convert this varchar
value to Date
?
UPDATE: I've noticed through trying some different things, the query seems to be fine with me using the value as a date for anything (DATEDIFF
, CONVERT
back to string, etc.) in the select portion, but trying to do anything with it in the WHERE
clause causes the error. To ensure nothing else is interfering, I created a temp table with only 1 row with the data value above, and running the query just against that one value gives the error
UPDATE 2: Ok, I have no idea why this fixes it, but this is what I found. When I run
SELECT
myValue
FROM
myTable
WHERE
TRY_CONVERT(DATE, myValue) IS NOT NULL
it returns EXACTLY the same values as
SELECT
myValue
FROM
myTable
WHERE
ISDATE(myValue) = 1
However, when I then add AND CAST(myValue AS DATE) < GETDATE()
to each WHERE
clause, only the first one works. I understand why TRY_CONVERT is safer to use, I'm still not sure why it works over GETDATE()
I can't reproduce your error...
declare @dt varchar(256) = '2018-12-26T00:00:00.000'
select cast(@dt as date)
So, there must be another rogue value in there that can't be converted.
To identify what value is causing the issue on versions < 2012, run this:
SELECT
myValue
FROM myTable
WHERE
ISDATE(myValue) = 0
Note, ISDATE
is deterministic only if you use it with the CONVERT
function, if the CONVERT
style parameter is specified, and style is not equal to 0, 100, 9, or 109.
For 2012 onward, use TRY_CONVERT
SELECT
*
FROM myTable
WHERE
TRY_CONVERT(date, myValue) IS NULL