Range on time field... - 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

General SQL Server Forums

New to SQL Server Programming

Range on time field...

Author

Topic

challapavan
Starting Member

8 Posts

Posted - 2007-09-17 : 05:11:43

I want to create one complex query with Date time field, my query is I want a grouping   between the time range

EX:

13/09/2007 1:00:00 PM     to   14/09/2007 4:00:00 AM     will be as Day1

14/09/2007 1:00:00 PM     to   15/09/2007 4:00:00 AM     Will be as Day2

''

''

''
This way the end date for the period will be 13/10/2007 4:00:00 as Day 30

pavan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 06:12:51

[code]SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) + DATEADD(DAY, Number, '19000112 13:00:00'),
DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) + DATEADD(DAY, Number, '19000113 16:00:00')
FROM master..spt_values
WHERE Type = 'p'
AND Number BETWEEN 1 AND 30[/code]

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 07:08:41

quote:

Originally posted by Peso

SELECT	DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) + DATEADD(DAY, Number, '19000112 13:00:00'),
	DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) + DATEADD(DAY, Number, '19000113 16:00:00')
FROM	master..spt_values
WHERE	Type = 'p'
	AND Number BETWEEN 1 AND 30

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

I think 16:00:00 should be 4:00:00

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 07:45:02

An excellent exercise to OP!
Thanks.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-17 : 08:36:14