Need help with a loop adding time to a date

Question

Need help with a loop adding time to a date

Rick Musa

Old Hand

Points: 374
More actions
July 28, 2006 at 10:34 am

#173526

What I want to do is to create a table displaying time only of the date.
BW1
00:00:00
00:30:00
01:00:00 thru 23:00:00
basically starting at 00:00:00 and incrementing by 30 mins to 23:30:00
here is what I have so far. Incrementing the day it's not what I want but it's as far as I've gotten.
I know the code changes the date I need to increment time only
--DROP TABLE #v_hcdata_Total
create table #v_hcdata_Total
(
BW1 smalldatetime
)
/* Insert data into #v_hcdata_Total for totals and weekly start and end dates */
declare @counter int, @Date datetime, @BW1 smalldatetime, @EW1 smalldatetime
SET @Date = getdate()
Set @BW1 = @Date
Set @EW1 = @BW1 + 6
set @counter = 0
while @counter < 48
begin
set @counter = @counter + 1
insert into #v_hcdata_Total
(BW1)
SELECT DATEADD(dd,-(DATEPART(dw, @BW1)- @counter),@Date) AS BW1
end
select * from #v_hcdata_Total
select convert(varchar,@BW1,108)
DROP TABLE #v_hcdata_Total

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

If you truely only want the time only, you should make your datatype a varchar/char, but here is the solution if you stick to smalldatetime.

SET NOCOUNT ON

DECLARE @Date datetime

SET @Date = '00:00:00'

WHILE @Date < '23:45:00'

BEGIN

INSERT INTO #v_hcdata_Total

SELECT @Date

SET @Date = DATEADD(mi,30,@Date)

END

SELECT convert(varchar,BW1,108) FROM #v_hcdata_Total

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Rick Musa Old Hand Points: 374 More actions · Answer 2

This is perfect! - I was really taking the long road with no return.

Thanks again,

Rick

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

I think this looks quite nice too...

SELECT CONVERT(VARCHAR, DATEADD(minute, 30 * (b0.i + b1.i + b2.i + b3.i + b4.i + b5.i), 0), 108) Time

FROM (SELECT 0 i UNION ALL SELECT 1) b0

CROSS JOIN (SELECT 0 i UNION ALL SELECT 2) b1

CROSS JOIN (SELECT 0 i UNION ALL SELECT 4) b2

CROSS JOIN (SELECT 0 i UNION ALL SELECT 8) b3

CROSS JOIN (SELECT 0 i UNION ALL SELECT 16) b4

CROSS JOIN (SELECT 0 i UNION ALL SELECT 32) b5

WHERE b0.i + b1.i + b2.i + b3.i + b4.i + b5.i < 48

ORDER BY b0.i + b1.i + b2.i + b3.i + b4.i + b5.i

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

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 4

My 2 cents...

SELECT DATEADD(mi,30*Number,0) AS BW1

INTO #v_hcdata_Total

FROM MASTER.dbo.spt_Values

WHERE NAME IS NULL

AND Number < 48

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)