Link to home
Start Free TrialLog in
Avatar of blossompark
blossomparkFlag for Ireland

asked on

SQL query error Conversion failed when converting date and/or time from character string.

Hi,
I have a table with two columns both of datetime type.
A START column and an END column.
The table contains over 160,000 rows.
I
I run the following query which returns approx. 2,000 rows before it falls over with the following message

Conversion failed when converting date and/or time from character string.
  select
      CASE WHEN  year(START)=1900 THEN CONVERT (time(0), '0:00:00',108) ELSE 
  CONVERT (time(0),CONVERT(varchar(6), DATEDIFF(second, START, END)/3600)+ ':'
  + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, START, END) % 3600) / 60), 2)
   + ':'+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, START, END) % 60), 2),108)  END AS DURATION
   FROM TIME_LOG

Open in new window


I am wondering how best to find the issue here?
I suspect somewhere in my data there are corrupt values?
any guidance appreciated.
Thanks
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of blossompark

ASKER

Thanks Scott...
will try out and report back
I would run a statement like below first to make sure that all strigs stored in that column are actually valid dates:
https://docs.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql?view=sql-server-2017

SELECT ISDATE (START) , [START],  ISDATE (END) , [END]
FROM TIME_LOG
WHERE  ISDATE (START) = 0 OR ISDATE (END)  = 0

Open in new window

thanks Scott, that got rid of the conversion error and looks much more readable ..
Icohan, thats a really useful bit of code, thanks for that

thanks guys, you make such a difference , hope ye know that