CODEWITHSUNDEEP
sqlsql-server
Veebster
Veebster

Reputation: 71

How to convert varchar data to time in SQL Server

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:

EmpID Date TimeIn TimeOut
123456 2021-12-13 05:55 14:30

Expected result:

EmpID Date TimeIn TimeOut Total_hrs
123456 2021-12-13 05:55 14:30 8:35

Upvotes: 2

Views: 1639

Answers (2)

LukStorms
LukStorms

Reputation: 29647

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
EmpID Date TimeIn TimeOut Total_hrs
123456 2021-12-14 05:55 14:30 08:35
234567 2020-12-13 not.a time! null

db<>fiddle here

Upvotes: 1

Farshid Shekari
Farshid Shekari

Reputation: 2449

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%60 as varchar(10)) as Total_hrs
from (
    select c.*, DATEDIFF(MINUTE, convert(time,TimeIn,108), convert(time,TimeOut,114)) minn from t c
)rt

Upvotes: 0

Related Questions