SQL Server : error conversion of a varchar data type

Question

I'm trying to run a SQL query however I'm getting an error

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

Code:

SELECT 
    ClientId,
    DatePassed,
    Lender
FROM 
    dbo.tbl_Profile_Mortgage
WHERE 
    DatePassed = '2011-04-28 00:00:00.000'

I have converted the data type as so

SELECT 
    ClientId,
    Convert (Date, DatePassed)
    Lender
FROM 
    dbo.tbl_Profile_Mortgage
WHERE 
    DatePassed = '2011-04-28 00:00:00.000'

This however is still giving me the same error

Column DatePassed is of type datetime null

Any suggestions would be appreciated

Answer 1

Conceptually, you think the where clause is performed before the convert(). But that is not necessarily how the query is run. The only SQL statement that guarantees order of execution is case (and then not all the time).

You can try this:

SELECT ClientId,
       (case when isdate(DatePassed) = 1 then Convert (Date, DatePassed) end),
       Lender
FROM dbo.tbl_Profile_Mortgage
WHERE DatePassed = '2011-04-28 00:00:00.000';

EDIT:

Given that DatePassed is already in a date time format, then your error is highly unusual. This is not a problem from the conversion of this column, because no varchar is involved.

The only thing I can think of is the constant. Perhaps you have a strange setting for default dates and it is trying to get the 4th day of the 28th month. If so, you can readily fix this by putting in an explicit conversion with a format:

SELECT ClientId,
       Convert(Date, DatePassed),
       Lender
FROM dbo.tbl_Profile_Mortgage
WHERE DatePassed = convert(date, '2011-04-28 00:00:00.000', 121);

Aaron Bertrand has a rather extended discussion of this issue in his blog. By the way, even though I recognize the problem that he mentions with the YYYY-MM-DD format, I still use that by default. I consider it a bug that Microsoft has chosen not to recognize this ISO standard format (but just because I think it is a bug doesn't necessarily convince anyone else).

Answer 2

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. I guess in your case, you're using a language setting that just plain doesn't work with that string literal you're using.

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.

The recommendation for SQL Server 2008 and newer is to use DATE if you only need the date portion, and DATETIME2(n) when you need both date and time. You should try to start phasing out the DATETIME datatype if ever possible.

In your concrete case - use this string in your WHERE clause:

WHERE DatePassed = '2011-04-28T00:00:00.000';

(notice the literal T between the date and time portions)