CODEWITHSUNDEEP
sqlsql-serversql-server-2000
JetJack
JetJack

Reputation: 988

How to make a cut off time

Using SQL Server 2000

I want to round off the time like 01:00:00 or 01:30:00 or 02:00:00 or 02:30:00 or 03:00:00

TABLE1

ID TIME

001 02:15:00 'Format (HH:mm:ss)
002 02:17:00
003 02:47:00
004 03:00:00
005 02:00:00
....

Time column's datatype is nvarchar

Condition

02:00:00 to 02:15:00 - I want to show 02:00:00 only
02:16:00 to 02:44:00 - I want to show 02:30:00 only
02:45:00 to 03:00:00 - I want to show 03:00:00 only
...

Expected output for table1

ID TIME

001 02:00:00 'Less than or equal to 2.15 mins so it should change to 02 hrs
002 02:30:00 'Less than 2.44 mins so it should change to 2.30 mins
003 03:00:00 'Greather than 2.45 mins so it should change to 03 hrs
004 03:00:00 'Same
005 02:00:00 'Same
....

How to make a query for this condition

Need query help

Upvotes: 2

Views: 737

Answers (4)

Madhivanan
Madhivanan

Reputation: 13700

Also try this

select 
    id,
    time,
    convert(varchar(10),dateadd(minute,datediff
        (
        minute,
        case when hours<=15 then -15 when hours>15 then 15  else 0 end,
        time)/30*30,0) ,108)
    as cut_off_time
from
(
    select id,time,
        datepart(minute,cast(time as datetime)) as hours from #time
) as t

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

convert(char(8), dateadd(minute, ((14+datediff(minute, 0, cast(Time as datetime)))/30)*30, 0), 108) as Time

SE-Data

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13506

Try This:

CREATE TABLE #time(ID int, TIME nvarchar(25))

INSERT INTO #time
VALUES(001,'02:15:00'),
(002,'02:17:00'),
(003,'02:47:00'),
(004,'03:00:00'),
(005,'02:00:00')

SELECT time,CASE  
             WHEN CAST(SUBSTRING(time,CHARINDEX(':',time,1)+1,2) as int) <= 15 then LEFT(time,2)+':00:00'
             WHEN CAST(SUBSTRING(time,CHARINDEX(':',time,1)+1,2) as int) >15 and CAST(SUBSTRING(time,CHARINDEX(':',time,1)+1,2) as int) <=45 then LEFT(time,2)+':30:00' 
             WHEN CAST(SUBSTRING(time,CHARINDEX(':',time,1)+1,2) as int) >45 and CAST(SUBSTRING(time,CHARINDEX(':',time,1)+1,2) as int) <=59 then '0'+cast((cast(LEFT(time,2) as int)+1) as varchar(3))+':00:00' 
             END
FROM #time

Upvotes: 1

PraveenVenu
PraveenVenu

Reputation: 8337

use the below

CREATE FUNCTION [dbo].[RoundTime] (@Time VARCHAR(8), @RoundTo float)
RETURNS VARCHAR(8)
AS
BEGIN
   DECLARE @RoundedTime smalldatetime
   DECLARE @Multiplier float

   SET @Multiplier= 24.0/@RoundTo

   SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar,@Time,121) AS datetime) AS float) * @Multiplier,0)/@Multiplier

   RETURN CONVERT(VARCHAR(8),@RoundedTime,108)
END



--select dbo.roundtime('02:47:00',0.5)

In a single statement

select CONVERT(VARCHAR(8),dateadd(mi, (datediff(mi,0,'02:47:00')+29)/30*30, 0),108)

Upvotes: 0

Related Questions