I have a table which had a date column of date type, and timein and timeout columns of type varchar(max).

I'm trying to calculate the total hours between timein and timeout, but I can't because I can't figure out how to convert the varchar to a time format.

So far I've tried CAST(x, AS time), CONVERT(time, x, 114) (also 108) and I keep getting errors

Conversion failed when converting date and/or time from character string

Any help you can give is appreciated in advance.

My table's date looks like this:

Expected result:

CodePudding user response：

This could give your response in the way you want. I just create my data to test my method and you can get rid of them by replacing your table name in query:

with t(EmpID, Date, TimeIn, TimeOut) as(

     select 123456 as EmpID, cast('2021-12-13' as date) as Date, '05:55' as TimeIn, '14:30' as TimeOut      
)
select rt.*, cast(minn/60 as varchar(10))   ':'   cast(minn` as varchar(10)) as Total_hrs
from (
    select c.*, DATEDIFF(MINUTE, convert(time,TimeIn,108), convert(time,TimeOut,114)) minn from t c
)rt

CodePudding user response：

To avoid errors from converting bad time strings, you can use TRY_CONVERT or TRY_CAST.
They'll just return NULL when they can't convert/cast it.

For example

create table yourtable (
 EmpID int, 
 [Date] date, 
 TimeIn varchar(5),
 TimeOut varchar(5)
);

insert into yourtable (EmpID, [Date], TimeIn, TimeOut) values
(123456, '2021-12-14', '05:55', '14:30'),
(234567, '2020-12-13', 'not.a', 'time!');


select *
, [Total_hrs] = convert(char(5),dateadd(minute, datediff(minute, try_cast(TimeIn as time), try_cast(TimeOut as time)), 0), 108)
from yourtable

db<>fiddle here