SQL - substring query result

Question

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.

Possible duplicate of [How to compare two dates to find time difference in SQL Server 2005, date manipulation](http://stackoverflow.com/questions/9521434/how-to-compare-two-dates-to-find-time-difference-in-sql-server-2005-date-manipu) — PM 77-1, Sep 07 '16 at 14:08

score 1 · Accepted Answer · answered Sep 07 '16 at 14:13

1

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

answered Sep 07 '16 at 14:13

Chris Pickford

8,283
5
44
71

score 0 · Answer 2 · answered Sep 07 '16 at 14:09

0

Can you try using convert(time,getdate())

answered Sep 07 '16 at 14:09

Kannan Kandasamy

12,647
3
21
35

score 0 · Answer 3 · answered Sep 07 '16 at 14:09

If you know the values are always less than a day, you can cast the difference to time:

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

Alternatively, you can convert to a string and extract the time component:

select right(convert(varchar(255), cast(out_am as datetime) - cast(in_am as datetime) ), 8)

apomene · Answer 4 · 2016-09-07T14:16:31.077

0

you can use CAST and FORMAT :

SELECT FORMAT(CAST(CAST(out_am AS DATETIME) - CAST(in_am AS DATETIME) AS TIME), N'hh\:mm\:ss')
FROM TABLE

edited Sep 07 '16 at 14:16

answered Sep 07 '16 at 14:10

apomene

14,086
9
43
68

SQL - substring query result

4 Answers4