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

Hi,

We have a view, below is the definition of the view

SELECT     A.column1, A.column2, A.column3, A.column4, A.column5, A.column6, 
A.columnDate,A.column7,
B.column8
FROM         dbo.TestTable AS A CROSS JOIN
                          (SELECT     'Delete' AS column8) AS B
WHERE     (DATEDIFF(day, A.columnDate, GETDATE()) <= 1)

when executed this query of the view, encountered the error as "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime"

The columnDate in table has a datatype of varchar(50) and same in view as well.

Without WHERE CLAUSE the query is working fine.

could you please check this issue and let me know how to resolve this problem.

July 5th, 2013 12:38am

Try changing your where clause as,

WHERE     (DATEDIFF(day, cast(A.columnDate as datetime), GETDATE()) <= 1)

Free Windows Admin Tool Kit Click here and download it now
July 5th, 2013 12:47am

Thanks RSingh for reply.

But no luck :(  still same error .

July 5th, 2013 1:08am

Thanks RSingh for reply.

But no luck :(  still same error .

As the date value is stored in CHAR type column we are not sure about format of the date values.

Try using DATE function to know the values which cant be converted as Datetime.

select ISDATE('2013-04-19'),ISDATE('2013-19-04')

SELECT     A.column1, A.column2, A.column3, A.column4, A.column5, A.column6, 
A.columnDate,A.column7

FROM         dbo.TestTable AS A 
WHERE     ISDATE(A.columnDate)=0

If still no rows are returned with incorrect datetime values, then try conversion using datetime2 type .

Free Windows Admin Tool Kit Click here and download it now
July 5th, 2013 1:47am

Try

SELECT     A.column1, A.column2, A.column3, A.column4, A.column5, A.column6, 
A.columnDate,A.column7,
B.column8
FROM         dbo.TestTable AS A CROSS JOIN
                          (SELECT     'Delete' AS column8) AS B
WHERE     (DATEDIFF(day, cast(A.columnDate as datetime), GETDATE()) <= 1)


July 5th, 2013 2:13am

I am considering that A.columnDate is char(10).

Store the result in a variable and then use it in where clause to compare.

DEclare @a char(10)

Set @a= '2013-06-01'
SELECT Cast(cast(GETDATE() as Date) as CHAR)
SELECT  DATEDIFF(day, @a, Cast(cast(GETDATE() as Date) as CHAR))

Free Windows Admin Tool Kit Click here and download it now
July 5th, 2013 3:12am

It is a clear indication that the field A.columnDate contains date & time string which are not in the correct datetime format.
July 5th, 2013 6:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics