Search code examples
sqlsql-serverdatediff

SQL datediff function data convertion error

I have calculated field in my query whereas it subtracts the days difference between two dates. I'm using SQL server to execute my query.

When I used the below function:

Cast(DATEDIFF(D,ACTUALFINISHDATE,PROJFINISHDATE) as int) AS 'Elasped Days

Both date fields are datetime date type (E.g 2019-02-23 00:00:00.000). I have tried Convert() instead of Cast(), but still experienced the same issue.

I'm getting the below error:

Msg 242, Level 16, State 3, Line 7 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Do I need to convert the date time to varchar before converting?

Solution

  • You must have bad data, either in ActualFinishDate or ProjFinishDate. To find the bad data...

    Select ActualFinishDate
From YourTable
Where ActualFinishDate > '' 
      And IsDate(ActualFinishDate) = 0