SQL Server: Conversion failed when converting time from character string

0
votes

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.

2
sqlsql-serverdatetime
This is my table I want to convert to datetime (currently its char(24)) imgur.com/a/TyFob5N – 123testing123
What is the format of the date in this column and which version of Sql Server are you using? – forpas
I'm using SQL 2016. Format of the date in this column is 2018-12-31 12:11:17.679 – 123testing123

2 Answers

1
votes

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.

0
votes

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())