CODEWITHSUNDEEP
sql-servertimesql-server-2012overlapdatediff
Nils
Nils

Reputation: 514

Datediff() overlapping days?

I'm having trouble figuring out how to use the datediff() function for overlapping days.

This is my table and the yellow marked rows i have trouble with .

SELECT 
    FOMTID,
    TOMTID,
    DATEDIFF(MINUTE, FOMTID,TOMTID) TIME 
FROM [DATABASE]...

enter image description here

I do NOT have a fields like '2001-01-01 23:30' & '2001-01-02 00:00' this would make the date diff function work but i only have the time.

Upvotes: 1

Views: 200

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

LiveDemo

SELECT 
  FOMTID,
  TOMTID, 
  [TIME] = IIF(FOMTID <= TOMTID,
              DATEDIFF(MINUTE, FOMTID,TOMTID),
              1440 - DATEDIFF(MINUTE, TOMTID,FOMTID))
FROM your_table;

or:

SELECT 
  FOMTID,
  TOMTID, 
  [TIME] = IIF(FOMTID <= TOMTID,
                     DATEDIFF(MINUTE, FOMTID,TOMTID),
              1440 + DATEDIFF(MINUTE, FOMTID,TOMTID))
FROM your_table;

EDIT:

LiveDemo2

WITH cte AS
(
SELECT 
  FOMTID,
  TOMTID, 
  [TIME] = IIF(FOMTID <= TOMTID,
                     DATEDIFF(MINUTE, FOMTID,TOMTID),
              1440 + DATEDIFF(MINUTE, FOMTID,TOMTID))
FROM #tab
)
SELECT FOMTID, TOMTID
   ,[MINUTES] = [TIME]
   ,[TIME]  = CAST([TIME] / 60  + [TIME] % 60 / 100.0 AS DECIMAL(8,2))
   ,[TIME2] = CAST([TIME] / 60  + [TIME] % 60 / 60.0 AS DECIMAL(8,2))
FROM cte

Choose how you want to treat minutes part:

[Time]  => 30min => 0.3
[Time2] => 30min => 0.5

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If these are time data types:

SELECT FOMTID, TOMTID,
       (CASE WHEN FOMTID <= TOMTID THEN DATEDIFF(MINUTE, FOMTID, TOMTID)
             ELSE 24*60 - DATEDIFF(MINUTE, TOMTID, FOMTID)
        END) AS TIME

Here is a little SQL Fiddle.

Upvotes: 1

Related Questions