SQL Server : convert seconds into hours

Question

I am using SQL Server and joining about 10 tables together using either inner join or left outer join.

I have a column in my select vp_timesheetpunch.TIMEINSECONDS (Time in seconds) that is in seconds and I want to add another column after saying how many hours that is. So that it list both seconds and hours.

select
    vp_timesheetpunch.personnum [Assoc ID],
    vp_timesheetpunch.personfullname [Assoc Name],
    vp_timesheetpunch.laborlevelname4 [Department],
    vp_timesheetpunch.eventdate [Shift Date],
    shiftassignmnt.shiftstartdate [Scheduled Start],
    vp_timesheetpunch.startdtm [Rounded Start],
    vp_timesheetpunch.inpunchdtm [Actual Start],
    vp_timesheetpunch.enddtm [Rounded End],
    vp_timesheetpunch.outpunchdtm [Actual End],
    vp_timesheetpunch.TIMEINSECONDS [Time in seconds]
from
    vp_timesheetpunch
left outer join
    vp_punchexceptions on vp_timesheetpunch.timesheetitemid = vp_punchexceptions.timesheetitemid
inner join
    timesheetitem on vp_timesheetpunch.timesheetitemid = timesheetitem.timesheetitemid
inner join
    workedshift on timesheetitem.workedshiftid = workedshift.workedshiftid
inner join
    shfasgnwshfmm on workedshift.workedshiftid = shfasgnwshfmm.workedshiftid
inner join
    shiftassignmnt on shfasgnwshfmm.shiftassignid = shiftassignmnt.shiftassignid
where
    --limit rows to the specified pay period
    vp_timesheetpunch.eventdate = '1/22/2019'
    --exclude rows that are missing data
    and vp_timesheetpunch.inpunchdtm is not null
    and vp_timesheetpunch.outpunchdtm is not null
    --limit rows to shifts with exceptions
order by
    vp_timesheetpunch.personnum,
    vp_timesheetpunch.eventdate

Is this possible to do on the fly?

I tried adding convert and naming AS Timeinhours but I cannot get the convert to work right.

Data lists time in seconds like "27900"

read here: https://blog.sqlauthority.com/2014/08/17/sql-server-convert-seconds-to-hour-minute-seconds-format/ — Raj More, Jan 23 '19 at 19:23
https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql — Raj More, Jan 23 '19 at 19:24
How do you want to display the hours; as HH:MM:SS, as decimal hours or as integer hours? — Peter Smith, Jan 23 '19 at 19:36
That is because @DaleBurrell is dividing by an int so the return type is int. Just divide by 3600.0 — Sean Lange, Jan 23 '19 at 19:37

score 4 · Accepted Answer · answered Jan 23 '19 at 19:38

4

You need to divide by 3600 but you need to be careful to avoid integer division. Just add .0 to your divisor.

declare @Seconds int = 27900

select [hours] =  convert(decimal(7,2), @Seconds / 3600.0)

answered Jan 23 '19 at 19:38

Sean Lange

33,028
3
25
40

3

And this also perfectly demonstrates creating a `Minimal` example which demonstrates the problem and avoids having to understand a large complex query when its not relevant. – Dale K Jan 23 '19 at 19:41
Use your column names instead of a variable. – Sean Lange Jan 23 '19 at 19:57
2

Of course it is....you didn't read my post carefully enough. You MUST include .0 to force your literal to be a decimal. Otherwise you are dividing by an integer. – Sean Lange Jan 23 '19 at 19:59

SQL Server : convert seconds into hours

1 Answers1