Gerard Santos Gerard Santos - 2 months ago 15
SQL Question

SQL - substring query result

I have table with the following columns

log_id INT PRIMARY KEY
emp_name VARCHAR(7) NOT NULL
date_log VARCHAR(23) NOT NULL
in_am VARCHAR(8) NULL
out_am VARCHAR(4) NULL
total_am VARCHAR(4) NULL
in_pm VARCHAR(4) NULL
out_pm VARCHAR(8) NULL
total_pm VARCHAR(4) NULL
grand_total VARCHAR(4) NULL
id INT Foreign key here


Supposed I already get the value of in_am and out_am and I want to get the difference between it I did this.

select cast(out_am as datetime) - cast(in_am as datetime) from Table


The result is like this:

1900-01-01 00:00:07.000


But I want this result

00:00:07


I try to substring it like this:

select substring((cast(out_am as datetime) - cast(in_am as datetime)),15,20) from table


but it doesn't work.

Answer

Simply use the DATETIME format of 108 to output HH:MM:SS as follows:

SELECT CONVERT(VARCHAR(8), (CAST(out_am AS DATETIME) - CAST(in_am AS DATETIME)), 108) FROM Table
Comments