YVS1102 YVS1102 - 3 months ago 8
SQL Question

Datepart result format as time

I have this query

SELECT
d.Nip,d.FullName,b.*,c.InTime,c.OutTime,
(SELECT DATEPART(HOUR, OutTime)) - (SELECT DATEPART(HOUR, InTime)) as lebih
FROM
DinasAuditHeader a
INNER JOIN
DinasAuditDetail b on a.KodeAuditHeader = b.KodeAuditHeader
INNER JOIN
Attendance c on b.Nip = c.Nip and b.attendancedate = c.AttendanceDate
INNER JOIN
employee d on c.Nip = d.Nip
WHERE
b.Nip = '1502427'
AND c.AttendanceCode = 'P3'
AND a.KodeAuditHeader = 'AD0000001'


From the query I get this result

Nip FullName KodeDetailDinas KodeAuditHeader nip AttendanceDate claim_status InTime OutTime lebih
1502427 FERNANDO ALIM 1 AD0000001 1502427 2016-08-28 0 2016-08-28 08:55:00.000 2016-08-28 21:03:00.000 13
1502427 FERNANDO ALIM 3 AD0000001 1502427 2016-08-30 0 2016-08-30 08:55:00.000 2016-08-30 18:03:00.000 10
1502427 FERNANDO ALIM 2 AD0000001 1502427 2016-08-29 0 2016-08-29 08:55:00.000 2016-08-29 19:03:00.000 11
1502427 FERNANDO ALIM 4 AD0000001 1502427 2016-08-31 0 2016-08-31 08:50:00.000 2016-08-31 20:03:00.000 12


As you can see from my query and the result. I have a coloumn called with
lebih
.
lebih
is a result range between
InTime
and
OutTime
.

I have two question(s).


  1. From my query above i only can get
    hour
    . So how to convert it with this format
    hh:mm

  2. How to see the range between them (
    OutTime
    -
    InTime
    ) and then substract with
    - 08:00
    ?


Answer

You can try SELECT CONVERT(VARCHAR(5),(OutTime - InTime),108) AS lebih

Below Sql query should work in your case:

SELECT 
    d.Nip,d.FullName,b.*,c.InTime,c.OutTime, 
    CONVERT(VARCHAR(5),(OutTime - InTime),108) AS lebih
FROM 
    DinasAuditHeader a 
INNER JOIN
    DinasAuditDetail b on a.KodeAuditHeader = b.KodeAuditHeader 
INNER JOIN
    Attendance c on b.Nip = c.Nip and b.attendancedate = c.AttendanceDate 
INNER JOIN
    employee d on c.Nip = d.Nip 
WHERE
    b.Nip = '1502427' 
    AND c.AttendanceCode = 'P3' 
    AND a.KodeAuditHeader = 'AD0000001'

UPDATE:

To subtract 8 hours from lebih, you can use

CONVERT(VARCHAR(5), DATEADD(HOUR, -8, OutTime - InTime), 108) AS lebih