time span over any date [solved]

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

SQL Server 2005 Forums

Transact-SQL (2005)

time span over any date [solved]

Author

Topic

Tyde
Starting Member

2 Posts

Posted - 2009-09-18 : 03:19:58

I have a order table with a regdate (datetiem format) that I need to make a selection on with a time span for a day over a given pirod of time.

The output will be some thing like
Date, timespan_1, timespan_2, timespan_3, timespan_4.....

Where eatch time span will be like
timespan_1 = 00:00:00 to 05:29:59
timespan_2 = 05:30:00 to 07:29:59
timespan_3 = 07:30:00 to 08:59:59
timespan_4 = 09:00:00 to 10:29:59

Is the a easy way to do this ??

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-18 : 04:06:08

Something like this probably:

SELECT CONVERT(varchar(20), MyDateColumn, 112)
  Timespan1 = SUM(CASE WHEN DATEDIFF(ss, DATEADD(dd,DATEDIFF(dd,0,MyDateColumn),0), MyDateColumn) BETWEEN 0 AND 19799 THEN 1 ELSE 0 END),    --> 00:00:00 to 05:29:59
  Timespan2 = SUM(CASE WHEN DATEDIFF(ss, DATEADD(dd,DATEDIFF(dd,0,MyDateColumn),0), MyDateColumn) BETWEEN 19800 AND 26999 THEN 1 ELSE 0 END), --> 05:30:00 to 07:29:59
  ...
FROM table

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-18 : 04:08:52

BTW: the DATEADD(dd,DATEDIFF(dd,0,MyDateColumn),0) method will give the beginning of the given day for any datetime value. If MyDateColumn = '2009-09-18 10:07:52' then DATEADD(dd,DATEDIFF(dd,0,MyDateColumn),0) will be '2009-09-18 00:00:00'

- Lumbago

Tyde
Starting Member

2 Posts

Posted - 2009-09-18 : 04:48:20

Works great thank for pointing the way for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources