Time Interval for hour and 30 minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Time Interval for hour and 30 minutes

Author

Topic

phanicrn
Starting Member

42 Posts

Posted - 2007-09-04 : 11:38:38

Time Interval for hour and 30 minutes
--------------------------------------------------------------------------------

Hi Everyone

I Have column in sql server databas as "HHMMSS" and data as and i am doing a substring to get values for hours and minutes. since my calculations based on hour interval and 30 minutes interval

for ex: Now i want to show all the transaction done b/w 6 to 7 am or pn.

and for 30 minutes interval i have get the calculation as transactions done b/w 6:00 to 6:30 and 6:30 to 7:00 either it's am or pm. now how i can write my sql statements that calculates hour and 30 minutes intervals
HHMM
-------------
06:43
09:26
09:26
11:58
12:25
18:17
20:45
00:43
00:53
16:47

Thanks
Phani

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 11:43:18

This is a start. I can't figure out what you want

-- Prepare sample data to mimic your environment
DECLARE	@Test TABLE (HHMM DATETIME)

INSERT	@Test
SELECT	'06:43' UNION ALL
SELECT	'09:26' UNION ALL 
SELECT	'09:26' UNION ALL
SELECT	'11:58' UNION ALL
SELECT	'12:25' UNION ALL
SELECT	'18:17' UNION ALL
SELECT	'20:45' UNION ALL
SELECT	'00:43' UNION ALL
SELECT	'00:53' UNION ALL
SELECT	'16:47'

-- Show some output
SELECT	HHMM,
	DATEADD(MINUTE, DATEDIFF(MINUTE, 0, HHMM) - DATEDIFF(MINUTE, 0, HHMM) % 30, 0) AS Starting30,
	DATEADD(MINUTE, 30 + DATEDIFF(MINUTE, 0, HHMM) - DATEDIFF(MINUTE, 0, HHMM) % 30, 0) AS Ending30
FROM	@Test

E 12°55'05.25"
N 56°04'39.16"

phanicrn
Starting Member

42 Posts

Posted - 2007-09-04 : 12:01:34

I wan to time interval calculations for 1 hour interval and 30 minutes interval.. I have table as "Time_Of_Day_Dim" and column as "HHMMSS" and sample data in the column will look as below. since my calculation is based on hour and minutes interval, i am using substring to get hours and minutes.

and my business logic is,

ex: Since ours is finance, client want to monitor transactions done between an hour and 30 minutes interval:

For ex: lets say we have transaction done at 06:43:02. now this sample data falls in 06:30 to 07:00 interval.

Sample output would be

Transactions count 30 mintues interval
----------------- ------------------

1 06:30 to 07:00

2 07:30: to 08:00

Orignal output in database.
-------------------------
HHMMSS
-------
06:43:02
09:26:09
09:26:27
11:58:53
12:25:31
18:17:59
20:45:08
00:43:24
00:53:26
16:47:32
20:54:30
05:46:13
06:12:28
06:30:21
07:27:31
10:25:27
16:40:39
18:10:21
19:39:20
22:25:16

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 12:19:02

[code]-- Prepare sample data to mimic your environment
DECLARE @Sample TABLE (HHMMSS DATETIME)

INSERT @Sample
SELECT '06:43:02' UNION ALL
SELECT '09:26:09' UNION ALL
SELECT '09:26:27' UNION ALL
SELECT '11:58:53' UNION ALL
SELECT '12:25:31' UNION ALL
SELECT '18:17:59' UNION ALL
SELECT '20:45:08' UNION ALL
SELECT '00:43:24' UNION ALL
SELECT '00:53:26' UNION ALL
SELECT '16:47:32' UNION ALL
SELECT '20:54:30' UNION ALL
SELECT '05:46:13' UNION ALL
SELECT '06:12:28' UNION ALL
SELECT '06:30:21' UNION ALL
SELECT '07:27:31' UNION ALL
SELECT '10:25:27' UNION ALL
SELECT '16:40:39' UNION ALL
SELECT '18:10:21' UNION ALL
SELECT '19:39:20' UNION ALL
SELECT '22:25:16'

-- Show some output
SELECT CONVERT(CHAR(5), b.Starting, 108) + ' to ' + CASE WHEN b.Ending >= 1 THEN '24:00' ELSE CONVERT(CHAR(5), b.Ending, 108) END AS Interval,
COUNT(s.HHMMSS) AS Items
FROM (
SELECT DATEADD(MINUTE, 30 * Number, 0) AS Starting,
DATEADD(MINUTE, 30 * Number + 30, 0) AS Ending
FROM master..spt_values
WHERE Type = 'p'
AND Number < 48
) AS b
LEFT JOIN @Sample AS s ON s.HHMMSS >= b.Starting AND s.HHMMSS < b.Ending
GROUP BY b.Starting,
b.Ending
ORDER BY b.Starting[/code]

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-04 : 12:24:53

Don't use VARCHAR and substrings in SQL when working with Dates, Times and time intervals; you are making things much harder and more complicated than they need to be. Use the correct data type, DATETIME.

more here:

http://weblogs.sqlteam.com/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspx

and some date functions and more on time intervals here:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

phanicrn
Starting Member

42 Posts

Posted - 2007-09-04 : 16:35:18

Hi

All i have to wirte procudre and insertcolumn data, and execute the procude, is it what you saying..? Really i dont know what you are trying to communicate, hey sorry , i am not a strong sql developer, i just support sql for my software. can you guide me.?

Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 16:59:43

quote:
Originally posted by phanicrn

can you guide me.?

Wha's wrong with he suggestion I made?

E 12°55'05.25"
N 56°04'39.16"

phanicrn
Starting Member

42 Posts

Posted - 2007-09-11 : 16:56:44

Thanks guys.. Problem Solved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources