I have a column below that has datatype char(24) but the data contains a date
I want to convert the data to datetime so that I can select the data from the past hour like this:
Where CounterDateTime >= DateAdd(hour, -1, getDate())
But I keep getting an error:
Conversion failed when converting date and/or time from character string
even if I convert my CounterDateTime to datetime. Please help.
Don't store date/time values as strings. This can cause a problem.
The next big issue is relying on implicit conversion. If you have to convert the values, do so explicitly. So:
Where try_convert(datetime, CounterDateTime) >= DateAdd(hour, -1, getDate())
You clearly have values that cannot be converted. You don't know why. You can find these values using a query:
select CounterDateTime
from t
where try_convert(datetime, CounterDateTime) is null and
CounterDateTime is not null;
This will return the non-NULL values that cannot be converted.
You can cast the column to datetime like this:
Where CounterDateTime IS NOT NULL AND Cast(RTRIM(LTRIM(CounterDateTime)) as DateTime) >= DateAdd(hour, -1, getDate())
this could also work:
Where CounterDateTime IS NOT NULL AND CONVERT(datetime, RTRIM(LTRIM(CounterDateTime)), 121) >= DateAdd(hour, -1, getDate())
and 1 more:
Where CounterDateTime IS NOT NULL AND try_parse(RTRIM(LTRIM(CounterDateTime)) as DateTime using 'en-US') >= DateAdd(hour, -1, getDate())
