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
blossomparkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
The error might relate to the first CONVERT(time(0).

At any rate, I'd add the extra leading 0 to the first expression and simplify the second. like so:

SELECT
      CASE WHEN year([START])=1900
           THEN CONVERT (time(0), '00:00:00', 108)
           ELSE CAST(DATEADD(second, DATEDIFF(second, [START], [END]), 0) AS time(0))
           END AS DURATION
FROM TIME_LOG

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blossomparkAuthor Commented:
Thanks Scott...
will try out and report back
lcohanDatabase AnalystCommented:
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

blossomparkAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.