CODEWITHSUNDEEP
sqlsql-server
Shi Jie Tio
Shi Jie Tio

Reputation: 2529

How to used the T-SQL to do the minus between two column in SQL server?

I have one table named Employee in SQL Server:

Employee   Clock-In                  Clock-Out
111        11/4/2019 2:31:08 PM      11/4/2019 4:31:08 PM
112        11/4/2019 2:31:08 PM      12/4/2019 2:31:08 PM

I wish to have a new column in this Employee table which is Duration: (below is my expected table)

Employee   Clock-In                  Clock-Out                 Duration(day)
111        11/4/2019 2:31:08 PM      11/4/2019 4:31:08 PM      0
112        11/4/2019 2:31:08 PM      12/4/2019 2:31:08 PM      1
113        13/4/2019 2:31:08 PM                                2

The Duration(day) column data will be auto calculate by using the Clock-Out column minus the Clock-In column; however if the Clock-Out time is empty, it will take the current datetime minus the Clock-In column. For example the current datetime is 15/4/2019 2:31:08 PM.

Anyone have ideas on this?

Upvotes: 0

Views: 70

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use datediff() and coalesce()

select employee,
ClockIn,ClockOut,
datediff(day,ClockIn,coalesce(ClockOut,getdate())) as duration
from table_name

for that you can create a view like below

create view view_name AS
select employee,
ClockIn,ClockOut,
datediff(day,ClockIn,coalesce(ClockOut,getdate())) as duration
from table_name

Upvotes: 3

EzLo
EzLo

Reputation: 14199

Use DATEDIFF, not with DAY but with MINUTE or SECOND. The following example returns 1 day but the amount of time is actually 2 seconds:

SELECT
    DATEDIFF(
        DAY,
        '2019-01-01 23:59:59',
        '2019-01-02 00:00:01')

DATEDIFF with DAY will not take into account the time portion, it will only look at the day difference.

So you can use a lower degree to measure a full day, like minutes or seconds:

DECLARE @FirstDate DATETIME = '2019-01-01 23:59:59'
DECLARE @SecondDate DATETIME = '2019-01-03 05:00:00'

SELECT
    SecondDifferences = DATEDIFF(SECOND, @FirstDate, @SecondDate),
    FullDaysBySecond = DATEDIFF(SECOND, @FirstDate, @SecondDate) / 86400, -- 86400 = 24*60*60

    MinuteDifferences = DATEDIFF(MINUTE, @FirstDate, @SecondDate),
    FullDaysByMinute = DATEDIFF(MINUTE, @FirstDate, @SecondDate) / 1440, -- 1440 = 24*60

    DayDifferences = DATEDIFF(DAY, @FirstDate, @SecondDate) -- Wrong, not full 24 hours days!

Result:

SecondDifferences   FullDaysBySecond    MinuteDifferences   FullDaysByMinute    DayDifferences
104401              1                   1741                1                   2

So the ALTER would be:

ALTER TABLE Employee ADD Duration AS DATEDIFF(
        MINUTE, 
        [Clock-In], 
        ISNULL([Clock-Out], GETDATE())) 
    / 1440

You won't be able to persist this column because GETDATE() is non-deterministic and SQL Server won't let you, meaning that this will be computed every time it is queried.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13016

use datediff() and sql server isnull() function, since your columns are with dash, you need to escape it.

select Employee
  , [Clock-In]
  , [Clock-Out]
  , datediff(dd, [Clock-In], isnull([Clock-Out], getdate())) as Duration
from Employee

Upvotes: 0

Related Questions