CODEWITHSUNDEEP
sqlsql-server-2008
user867621
user867621

Reputation: 1197

Getting an error saying the conversion of a varchar data type to datetime resulted in an out of range value

I have this sql statement

select ord_type,
       case 
            when entered_dt  between '2013-02-01' and '2013-02-31' then 1 
       end  
from oehdrhst_sql

I keep getting this error

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

Whats wrong with it... I can't seem to find a solution

Upvotes: 0

Views: 656

Answers (2)

marc_s
marc_s

Reputation: 754368

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

The ISO-8601 format is supported by SQL Server comes in two flavors:

  • YYYYMMDD for just dates (no time portion); note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

or:

  • YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes (but they can be omitted), and a fixed T as delimiter between the date and time portion of your DATETIME.

This is valid for SQL Server 2000 and newer.

If you use SQL Server 2008 or newer and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69514

SELECT  ord_type
      , CASE WHEN entered_dt  BETWEEN '20130201' AND '20130228' --<-- There are only 28 days in Feb 
             THEN 1 ELSE NULL END  
FROM oehdrhst_sql

Upvotes: 3

Related Questions