CODEWITHSUNDEEP
sqlsql-serverdatetime
Gerard Santos
Gerard Santos

Reputation: 326

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.

Upvotes: 0

Views: 92

Answers (4)

apomene
apomene

Reputation: 14389

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

Upvotes: 0

Chris Pickford
Chris Pickford

Reputation: 9001

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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)

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

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

Upvotes: 0

Related Questions