CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Gopal
Gopal

Reputation: 11972

How to display the time with condition

Using SQL Server

Table1

ID  |  Intime  |   Outtime
--------------------------
001 | 00:21:00 |  00:48:00
002 | 08:14:00 |  13:45:00
003 | 00:34:00 |  00:18:00

Time format: HH:MM:SS

Tried Code.

SELECT dateadd(mi, datediff(mi,0, Intime)/30*30, 0) AS 'Intime'
  FROM Table1

Above Query giving the result for 30 minutes or 1 hours, which means minutes less than 30 then it is displaying 30 minutes, minutes greater than 30 then it is displaying 1 Hours

I need to display the time like 15 minutes, 30 minutes or 1 Hours, it should display a roundoff time with condition

Conditions are

If minutes is less than 15 minutes then it should display exactly 15 Minutes, for example 03:15:00
If minutes is greater than 15 minutes and less than 45 minutes then it should display exactly 30 Minutes, for example 03:30:00
If minutes is greater than 45 minutes then it should display exactly 1 hour, for example 04:00:00

Expected Output from the table1

ID | Intime | Outtime

001 | 00:30:00 | 01:00:00
002 | 08:15:00 | 14:00:00
003 | 00:30:00 | 00:30:00

How to make a query for the roundoff time with condition.

Need Query Help

Upvotes: 0

Views: 445

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

A literal interpretation of your question:

select id,
InTime = DateAdd(mi,
    case
    when DateAdd(hh, -DATEDIFF(HH, 0, intime), intime) = '00:00:00' then 0
    when DateAdd(hh, -DATEDIFF(HH, 0, intime), intime) <= '00:15:00' then 15
    when DateAdd(hh, -DATEDIFF(HH, 0, intime), intime) <= '00:45:00' then 30
    else 60
    end, DateAdd(hh, DATEDIFF(HH, 0, intime), 0)),
OutTime = DateAdd(mi,
    case
    when DateAdd(hh, -DATEDIFF(HH, 0, OutTime), OutTime) = '00:00:00' then 0
    when DateAdd(hh, -DATEDIFF(HH, 0, OutTime), OutTime) <= '00:15:00' then 15
    when DateAdd(hh, -DATEDIFF(HH, 0, OutTime), OutTime) <= '00:45:00' then 30
    else 60
    end, DateAdd(hh, DATEDIFF(HH, 0, OutTime), 0))
from tbl

But given your output, your third rule should have been

If minutes is greater than [OR EQUAL TO] 45 minutes then it should display exactly 1 hour, for example 04:00:00

So the query becomes

select id,
InTime = DateAdd(mi,
    case
    when DateAdd(hh, -DATEDIFF(HH, 0, intime), intime) = '00:00:00' then 0
    when DateAdd(hh, -DATEDIFF(HH, 0, intime), intime) <= '00:15:00' then 15
    when DateAdd(hh, -DATEDIFF(HH, 0, intime), intime) < '00:45:00' then 30
    else 60
    end, DateAdd(hh, DATEDIFF(HH, 0, intime), 0)),
OutTime = DateAdd(mi,
    case
    when DateAdd(hh, -DATEDIFF(HH, 0, OutTime), OutTime) = '00:00:00' then 0
    when DateAdd(hh, -DATEDIFF(HH, 0, OutTime), OutTime) <= '00:15:00' then 15
    when DateAdd(hh, -DATEDIFF(HH, 0, OutTime), OutTime) < '00:45:00' then 30
    else 60
    end, DateAdd(hh, DATEDIFF(HH, 0, OutTime), 0))
from tbl

RE your previous question

You accepted an answer in your previous quesion that always ROUNDED DOWN when there was an answer there that rounded properly? The description is not correct

Above Query giving the result for 30 minutes or 1 hours, which means minutes less than 30 then it is displaying 30 minutes

It doesn't. select dateadd(mi, datediff(mi,0, '09:21')/30*30, 0) returns 09:00 which rounds DOWN.

Upvotes: 1

Related Questions