What to do if the conversion of a varchar data type to a datetime data type resulted in an out-of-range value?

343 Asked by DavidEdmunds in Cyber Security , Asked on Mar 7, 2023

I am trying to run a simple query to get all rows created in November:

SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN '2014-11-01 00:00:00.000' AND '2014-11-30 23:59:59.997';
SMSS returns:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime: Do I need to tell the server that 'Created' is datetime? If not, why am I getting this varchar message?

Answered by Diya tomar

If the conversion of a varchar data type to a datetime data type resulted in an out-of-range value- I recommend using ODBC datetime literals. Despite their name they do not require you to connect via ODBC. They bypass the usual conversion rules in SQL Server and are always interpreted as a datetime.

    SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN {TS '2014-11-01 00:00:00.000'} AND {TS '2014-11-30 23:59:59.997'};

The other supported ODBC datetime literals are D and T as documented here in Books Online. Both return datetime (not date or time), but the syntax is still compact and unambiguous. The fixed formats for the strings are:

Example:

SELECT TOP (1) D = {D '2014-12-27'}, T = {T '14:49:23.789'}, TS = {TS '2014-12-27 14:49:23.789'};

The T variant returns the time specified on the current day, as reported by the internal-use-only {fn getdateODBC()}:

What to do if the conversion of a varchar data type to a datetime data type resulted in an out-of-range value?

Your Answer