CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Fridden
Fridden

Reputation: 189

Why do I get an date conversion error?

I have a table with 3 columns:

I try to execute the following code:

SELECT DISTINCT 
    d.PNum, d.DEnd, d.DFrom 
FROM d
WHERE 
    (d.PNum LIKE '1995%' AND (d.DFrom <= DATEADD(day,14,GETDATE()))
ORDER BY 
    d.PNum

and I get:

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

If I change the condition to 

WHERE (d.PNum LIKE '199%' AND (d.DFrom <= DATEADD(day,14,GETDATE()))

I,e retrieve all with PNum beginning with 199 I will get the results, including those records that have a PNum beginning with 1995..

Depending on what I use I will either retrieve records or a date conversion error. For instance:

d.PNum LIKE '199507%' ...

Will work (there are several records).. but

d.PNum LIKE '199512%'

will not work (there are several records).

Note, the error will only occur when I combine the two conditions. If I remove the (d.DFrom) part it will work just fine. And again, if I use 199% i will get all the records including those that I cannot get when narrowing down the filter.

So it seems to me that SQL Server will interpret the date somehow depending on the PNum filter?? But why?

Thanks for any insight you might give!

/Fridden

Upvotes: 1

Views: 156

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

  1. Always use proper DATETIME datatype to store date and time
  2. You need to CAST to DATETIME before comparison.

Try this

SELECT DISTINCT d.PNum,d.DEnd,d.DFrom FROM d
WHERE d.PNum LIKE '1995%' 
      AND (cast(d.DFrom as datetime) <= DATEADD(day,14,GETDATE())
ORDER BY d.PNum

Upvotes: 3

Devart
Devart

Reputation: 122032

Try to change your datatypes -

DECLARE @temp TABLE(
    PNum DATETIME,
    DFrom DATE,
    DEnd DATE
)

INSERT INTO @temp (PNum, DFrom, DEnd)
VALUES ('20130112 12:30', '20130112', '20130112');

SELECT DISTINCT PNum, DEnd, DFrom 
FROM @temp
WHERE YEAR(PNum) = 2013 AND DFrom <= DATEADD(DAY, 14, GETDATE())
ORDER BY PNum

Upvotes: 2

Related Questions