CODEWITHSUNDEEP
sqlsql-serverdatetime
123testing123
123testing123

Reputation: 77

SQL Server: Conversion failed when converting time from character string

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.

Upvotes: 0

Views: 2609

Answers (2)

forpas
forpas

Reputation: 164099

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Related Questions