CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
ernest
ernest

Reputation: 1724

Converting m/d/y strings to datetime yields conversion error

Here is the code I'm using:

SELECT SalesItemPK
,FieldName
,Value /*Convert date to the first of the selected month*/
FROM [Oasis].[dbo].[vw_SALES_SalesItemDetails]
WHERE SalesItemPK IN(
1425
,1225
,1556
,1589
,1599
,1588
,1590)
AND FieldName = 'Estimated Ship Date'
AND CONVERT(DATETIME, Value) >= CONVERT(DATETIME, '1/1/2010')

(I'm selecting just those PKs because those are all the rows in the query.)

This is the error I receive:

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

Below is a sample of the data returned from my view. The final solution actually converts the values to the first of their respective months. But an error is thrown either way.

enter image description here

When I remove the WHERE, it works fine. So there's something strange going on there I can't seem to figure out so far. Any ideas?

Upvotes: 0

Views: 552

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280350

Odds are, either the date is being interpreted as d/m/y (and the first row fails because there is no 18th month), or you have a piece of data in there where the first part (month) is > 12.

To find offending rows:

SELECT Value FROM [Oasis].[dbo].[vw_SALES_SalesItemDetails]
  WHERE SalesItemPK IN (1425,1225,1556,1589,1599,1588,1590)
  AND ISDATE(Value) = 0;

(And if you find offending rows, obviously, fix them.)

You can also make sure the values are interpreted as m/d/y (and won't fail on other garbage in the column) using:

SELECT SalesItemPK, FieldName, Value,
  FirstOfMonth = CASE WHEN ISDATE(Value) = 1 THEN 
    DATEADD(DAY,1-DAY(CONVERT(DATETIME,Value,101)),CONVERT(DATETIME,Value,101)) END 
FROM [Oasis].[dbo].[vw_SALES_SalesItemDetails] 
WHERE SalesItemPK IN (1425,1225,1556,1589,1599,1588,1590)
  AND FieldName = 'Estimated Ship Date'
  AND CASE WHEN ISDATE(Value) = 1 THEN CONVERT(DATETIME, Value, 101) 
    ELSE NULL END >= '20100101';

Also note that just because you perform a WHERE clause on the PK values, does not mean SQL Server has to evaluate that condition first. It could try to convert every row in the view (heck, every row in the source table) to a DATETIME first. Which is why I also added a CASE expression to the SELECT list, just in case. No pun intended. I also offered my suggestion on how to calculate the first of the month easily (a lot of people tend to do really strange things, like convert to string).

Do you have any idea how much simpler this query would be if the view exposed a separate column as datetime, e.g.

, DateValue = CASE WHEN ISDATE(Value) = 1 THEN CONVERT(DATETIME, Value, 101)

Then the query could be:

SELECT SalesItemPK, FieldName, DateValue, 
  FirstOfMonth = DATEADD(DAY, 1-DAY(DateValue), DateValue)
FROM [Oasis].[dbo].[vw_SALES_SalesItemDetails]
WHERE SalesItemPK IN (1425,1225,1556,1589,1599,1588,1590)
AND DateValue >= '20100101';

In fact the view could also expose the FirstOfMonth calculation for you too.

These are some of the many, many, many reasons why you should never, ever, ever store date/time data as strings. At the very least, change the view to present these strings as a completely language-, dateformat- and region-neutral string (yyyymmdd) instead of mm/dd/yyyy.

Upvotes: 6

Vasanth
Vasanth

Reputation: 1710

Its probably one of your date values is not in correct format. Try the following query to find list of values that cant be converted as date.

 select value from [Oasis].[dbo].[vw_SALES_SalesItemDetails] where isdate(value) = 0

Upvotes: 0

Related Questions