TSQL DateTime issue

Question

I have a simplified case here: a table with 5 fields of startdate, starttime, enddate, endtime and totalduration, all as varchar(20) type. Date fields are in the format like '02/02/2009' and time format like '02:02:00'. There are no null values at all. There is no problem for the following query:

  select 
    cast(startdate + ' ' + starttime as datetime) StartDt, 
    cast(enddate + ' ' + endtime as datetime) EndDt,
    convert(datetime, cast(enddate + ' ' + endtime as datetime) - 
      cast(startdate + ' ' + starttime as datetime), 108) as DurationCalc,
    Totalduration
  from myTable

This works fine and I get thousand rows data:

  StarDt               EndDt     DurationCalc        Totalduration
  2009-01-01 12:00:00  ...       03:34:12            03:34:13
   ....

However, when I tried to apply a WHERE clause to filter out rows by differences, I got out-of-range error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value":

  where 
     cast(startdate + ' ' + starttime as datetime) < 
     cast(enddate + ' ' + endtime as datetime) 
     -- this additional condition caused problem
     and
     datediff(s,
     convert(datetime, cast(enddate + ' ' + endtime as datetime) - 
       cast(startdate + ' ' + starttime as datetime), 108),
     convert(datetime, Totalduration, 108)) > 1

Since there are so many rows in my table, it really is hard to identify row by row for the problem. I am not sure what I miss here that invalid rows are not filtered out, or if there is any way to find out the rows where out-of-range exist? Any suggestions?

If I remove the "additional condition" datediff(...), I can get rows as result. There seems no any problems in the first few rows. I applied the Top 1 to select, I still get out-of-range error.

Answer 1

Sql Server has an IsDate() function. Use it to identify your problem rows. Then fix the table to keep the data as DateTime columns in the first place.

Answer 2

I've seen this error before usually its due to US vs UK date formats, it depends on how the data was inserted into the database.

Another issue (related) is that when using datetime, and you use date as a parameter, you always need to add 1 extra day to your end date when doing date ranges (or date difference calculations)