0

Why would the following SQL cause the error?

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

I believe it has to do with the DATEDIFF and the type of DATE for StartDate?

(CASE WHEN @OrganizationId IS NOT NULL AND ev.EndDate >= @SortStartDate THEN 0
      WHEN ev.StartDate >= @SortStartDate THEN 1
      ELSE DATEDIFF(DAY, ev.StartDate, -1) 
      END) ASC,
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341

1 Answers1

0

I found the issue. The date on an event was set to 0209-06-01.

Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • I beg to differ, it was there in plain site. This column used to be a DATETIME but was converted to DATE a while back. – Mike Flynn Jun 06 '19 at 16:10
  • Look it up buddy, https://stackoverflow.com/questions/32568170/sql-doesnt-allow-to-cast-date-column-to-datetime – Mike Flynn Jun 06 '19 at 16:12
  • Not sure what variable you are referring to but DATEDIFF must convert the value to a DATETIME, not DATE – Mike Flynn Jun 06 '19 at 16:15