Geek Answers Handbook

Getting time interval from date and time

I have a table called Timezone, and the data looks like this:

Call_ID Start_Time 93856 2011-08-04 09:59:47.000 58796 2011-08-05 14:54:37.000 25489 2011-08-09 15:32:13.000

I want the result to be as follows:

 Call_ID Start_Time Interval 93856 2011-08-04 09:59:47.000 0930 58796 2011-08-05 14:54:37.000 1430 25489 2011-08-09 15:32:13.000 1530

I did something like this:

 Select Call_ID , Start_Time, CASE WHEN DATEPART(minute,Start_Time)>30 THEN RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '30' ELSE RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '00' END From Timezone Group By Call_ID , Start_Time, CASE WHEN DATEPART(minute,Start_Time)>30 THEN RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '30' ELSE RIGHT('0' + CAST(DATEPART(HOUR,Start_Time) AS VARCHAR),2) + '00' END

Is there a better way to do this?

+4
source share
3 answers
 select Call_ID, Start_Time, right(100+datepart(hour, Start_Time), 2)+ right(100+30*(datepart(minute, Start_Time)/30), 2) as Interval from TimeZone
+4
source

This is actually not so shorter, but, of course, more accurate with much smaller numbers and string concatenation:

 ;WITH intervals(h) AS ( SELECT TOP (48) CONVERT(TIME(0), DATEADD(MINUTE, 30*(number), '00:00')) FROM master..spt_values WHERE number >= 0 GROUP BY number ORDER BY number ) SELECT t.Call_ID, t.Start_Time, Interval = REPLACE(CONVERT(VARCHAR(5), ih), ':', '') FROM intervals AS i INNER JOIN dbo.TimeZone AS t ON DATEDIFF(MINUTE, ih, CONVERT(TIME(0), t.Start_Time)) BETWEEN 1 AND 30;

Not sure what you would like to do if you have a value right on the border. Do you want it to fall in the current interval or in the previous one? You can change BETWEEN 1 AND 30 to BETWEEN 0 AND 29 if you need other behavior.

0
source

Hi, what if this is the only time as shown below TIME 10:00:00 10:20:00 10:40:00

And I want to change it to 30 minutes, NVARCHAR time.

-1
source

More articles:


All Articles