CODEWITHSUNDEEP
sqlsql-serversql-server-2014
pawan kumar
pawan kumar

Reputation: 35

How to break datetime in 15 minute interval in sql sever 2014

I have split the below query in 15 minute interval on the basis of Start datetime but this query is not providing the exact result set as i am expecting. Below is the example of query i want to execute.

select Date_Stamp,
 Case when substring(convert(char(8),starttime,114), 1, 8) between '12:00:01 AM'and '12:15:00 AM' then '0015'
 when substring(convert(char(8),starttime,114), 1, 8) between '12:15:01 AM'and '12:30:00 AM' then '0030'
 when substring(convert(char(8),starttime,114), 1, 8) between '12:30:01 AM'and '12:45:00 AM' then '0045'
 when substring(convert(char(8),starttime,114), 1, 8) between '12:45:01 AM'and '01:00:00 AM' then '0100'

and i want the result as 

Date    Need result set
12:01 AM     '0015'
'12:15:01   '0030'
'12:30:01   '0045'
'12:45:01   '0100'
'01:00:01   '0115'
'01:15:01   '0130'
'01:30:01   '0145'
'01:45:01   '0200'
'02:00:01   '0215'
'02:15:01   '0230'
'02:30:01   '0245'
3:00:00 '    '0015'
'12:30:00   '0030'
'12:45:00   '0045'
'01:00:00   '0100'
'01:15:00   '0115'
'01:30:00   '0130'
'01:45:00   '0145'
'02:00:00   '0200'
'02:15:00   '0215'
'02:30:00   '0230'
'02:45:00   '0245'

Upvotes: 0

Views: 585

Answers (3)

Zorkolot
Zorkolot

Reputation: 2027

It appears you're using datetime and have only taken the substring of the time. A string cannot be compared to a time, without being casted to the time datatype.

For example:

    DECLARE @mytable TABLE (starttime datetime)
    INSERT INTO @mytable VALUES ('2018-03-13 00:00:01'), ('2018-03-15 00:00:01')
    SELECT * FROM @mytable

select CAST(starttime as time(0)) AS [thetime],
 Case when CAST(starttime as time) between '12:00:01 AM'and '12:15:00 AM' then '0015'
 when CAST(starttime as time) between '12:15:01 AM'and '12:30:00 AM' then '0030'
 when CAST(starttime as time) between '12:30:01 AM'and '12:45:00 AM' then '0045'
 when CAST(starttime as time) between '12:45:01 AM'and '01:00:00 AM' then '0100'
 END AS [Interval]
FROM @mytable

Produces:

thetime     Interval
00:00:01    0015
00:15:01    0030

Upvotes: 0

hkravitz
hkravitz

Reputation: 1385

You can use this date generator:

DEMO

    DECLARE @Break INT = 15 
    ;WITH Numbers (n) as 
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
    FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
)
    ,Dates as
    (
    SELECT dt
    FROM Numbers 
        CROSS APPLY 
            (
            VALUES (DATEADD(MINUTE , n, CAST(CAST(GETDATE() AS DATE) AS DATETIME)))
            ) X(Dt)
     WHERE N % @Break = 0 
     AND CAST(DT AS DATE) = CAST(GETDATE() AS DATE) --Only for today's date
    )
    SELECT CONVERT(VARCHAR(10),Dt,108) [Time] , REPLACE(CONVERT(VARCHAR(5),ISNULL(Lead(Dt) OVER (ORDER BY Dt) , DATEADD(MINUTE,@Break,Dt)),108), ':','') Grp 
    FROM Dates

Upvotes: 1

B3S
B3S

Reputation: 1051

Just change @starttime with your column name

DECLARE @starttime datetime = getdate()
SELECT CONCAT(CASE  WHEN DATEPART(HH, @starttime) <= 9
                    THEN '00'+ CAST(DATEPART(HH, @starttime) AS VARCHAR(2))
                    ELSE '0'+CAST(DATEPART(HH, @STARTTIME) AS VARCHAR(2))
              END,
              CASE  WHEN DATEPART(MINUTE, @STARTTIME) BETWEEN 1 AND 15
                    THEN 15
                    WHEN DATEPART(MINUTE, @STARTTIME) BETWEEN 16 AND 30
                    THEN 30
                    WHEN DATEPART(MINUTE, @STARTTIME) BETWEEN 31 AND 45
                    THEN 45
                    WHEN DATEPART(MINUTE, @STARTTIME) BETWEEN 46 AND 59 OR DATEPART(MINUTE, @STARTTIME) = 0
                    THEN 00
              END)

Upvotes: 2

Related Questions