blossompark
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
I am wondering how best to find the issue here?
I suspect somewhere in my data there are corrupt values?
any guidance appreciated.
Thanks
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
Icohan, thats a really useful bit of code, thanks for that
thanks guys, you make such a difference , hope ye know that
ASKER
will try out and report back