Return time intervals - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Return time intervals

Author

Topic

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-06-15 : 07:31:50

Guys,

Is it possible, in a view, to return a single field that will be a datetime showing the current date and every 15 minute interval between 6am and 9pm ?

So...
15/06/2006 06.00
15/06/2006 06.15
15/06/2006 06.30
15/06/2006 06.45
15/06/2006 07.00
...
15/06/2006 20.45
15/06/2006 21.00

Ideally, I want 2 columns, one to show as above, and the other to show just the time.

Thanks
Bob

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 07:56:39

That would be

CREATE VIEW vwToday
AS

select		top 100 percent q.t [DateTime],
		convert(varchar(10), q.t, 103) [Date],
		replace(convert(varchar(5), q.t, 108), ':', '.') [Time]
from		(
			select		DATEADD(mi, z.m, DATEADD(hh, w.h, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))) t
			from		(
						select 0 m union all select 15 union all select 30 union all select 45
					) z
			cross join	(
						select 6 h union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21
					) w
		) q
where		convert(varchar(6), q.t, 108) <= '21:00:00' 
order by	q.t

Peter Larsson
Helsingborg, Sweden

staticbob
Yak Posting Veteran

99 Posts

Posted - 2006-06-15 : 07:59:16

Thanks Peter, perfect.

"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 08:03:11

Here is another variant that show yesterdays, todays and tomorrows timeslots.

CREATE VIEW vwNowAbout
AS

select		top 100 percent q.dt [DateTime],
		convert(varchar(10), q.dt, 103) [Date],
		replace(convert(varchar(5), q.dt, 108), ':', '.') [Time]
from		(
			select		DATEADD(mi, z.m, DATEADD(hh, w.h, y.d)) dt
			from		(
						select 0 m union all select 15 union all select 30 union all select 45
					) z
			cross join	(
						select 6 h union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21
					) w
			cross join	(
						SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), -1) d union all SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) union all SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1)
					) y
		) q
where		convert(varchar(6), q.dt, 108) <= '21:00:00' 
order by	q.dt

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 10:43:44

[code]
select
[Time 15 min] =
dateadd(minute,a.number*15,b.DateStart)
from
-- Number table function from script library
dbo.F_TABLE_NUMBER_RANGE(0,95) a
cross join
(
select DateStart =
dateadd(dd,datediff(dd,0,getdate()),0)
) b
[/code]
Results:
[code]
Time 15 min
------------------------
2006-06-15 00:00:00.000
2006-06-15 00:15:00.000
2006-06-15 00:30:00.000
2006-06-15 00:45:00.000
2006-06-15 01:00:00.000
2006-06-15 01:15:00.000
2006-06-15 01:30:00.000
2006-06-15 01:45:00.000
2006-06-15 02:00:00.000
...
...
2006-06-15 22:45:00.000
2006-06-15 23:00:00.000
2006-06-15 23:15:00.000
2006-06-15 23:30:00.000
2006-06-15 23:45:00.000

(96 row(s) affected)

[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 11:56:03

quote:

Originally posted by Michael Valentine Jones

select
	[Time 15 min] =
		dateadd(minute,a.number*15,b.DateStart)
from
	-- Number table function from script library
	dbo.F_TABLE_NUMBER_RANGE(0,95) a
	cross join
	(
	select DateStart =
		dateadd(dd,datediff(dd,0,getdate()),0)
	) b

It is an impressing function, very versatile, but according to original posting, only hours between 06:00 to 21:00 should be considered. And as such, isn't your function overkill for this?

select
	[Time 15 min] =
		dateadd(minute, 360 + a.number*15,b.DateStart)
from
	-- Number table function from script library
	dbo.F_TABLE_NUMBER_RANGE(0, 60) a
	cross join
	(
	select DateStart =
		dateadd(dd,datediff(dd,0,getdate()),0)
	) b

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 12:16:21

I'm not sure what you mean by overkill, but if he really wants to limit the time range from 06:00 TO 21:00, this would do it.


select
	[Time 15 min] =
		dateadd(minute,a.number*15,b.DateStart)
from
	-- Number table function from script library
	dbo.F_TABLE_NUMBER_RANGE(24,84) a
	cross join
	(
	select DateStart =
		dateadd(dd,datediff(dd,0,getdate()),0)
	) b

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 12:40:01

quote:
Originally posted by Michael Valentine Jones

I'm not sure what you mean by overkill

I often use your F_DATETIME function. That's a beauty! And I thought this number function is as powerful.

I have a suggestion for you. What if your number function, and maybe even your datetime function, would have an extra parameter called stepping/increment?
Say I would like numbers from 0 to 5000 in step of 10?

Yes I know I can write select 10 * ... (0, 500)

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 14:00:41

Do you mean F_TABLE_DATE function, instead of F_DATETIME function? I'm not sure how a step would work with that.

As for the Number Table Function with Step, here you go:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67876

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources