Creating a table with 3 dates, + everytime.

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

Creating a table with 3 dates, + everytime.

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-20 : 13:31:13

i need to populate a table that automatically inserts a certain day every week, 8/13 +7 days every time for a bunch of of times
what function can i do that with?

so i need like, 8/13 8/20 8/27, etc automatically

there's dateadd but that only does it one time, need to do it for a a lot of times, i think i have to use a while loop but this is not working for me

declare @counter int
set @counter = 0
create table #morganextimeorganizer (checkindate smalldatetime  null, organizer smalldatetime  null, dayoftheweek nvarchar(15)  null)

while @counter < 10
begin
  set @counter = @counter + 7
	  update table #morganextimeorganizer set checkindate = '8/13/2008'



end

specifically i have 3 dates, 8/13, 8/16, 8/17

that need 7 days added everytime (basically every wed, sat, sun, for a long time)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 13:35:58

how long you want to add like this? what determines the limit?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-20 : 13:37:19

quote:
Originally posted by visakh16

how long you want to add like this? what determines the limit?

any arbitrary default is fine, i guess 595 times wolud take us to jan 1st, 2020 should be good

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 13:43:08

Try this:-

SELECT DATEADD(dd,7* number,'13/08/2008'),
 DATEADD(dd,7* number,'20/08/2008'),
 DATEADD(dd,7* number,'27/08/2008')
FROM master..spt_values
WHERE type='p'
and DATEADD(dd,7* number,'27/08/2008')<='01/01/2020'

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-20 : 18:09:37

quote:

Originally posted by visakh16

Try this:-

SELECT DATEADD(dd,7* number,'13/08/2008'),
 DATEADD(dd,7* number,'20/08/2008'),
 DATEADD(dd,7* number,'27/08/2008')
FROM master..spt_values
WHERE type='p'
and DATEADD(dd,7* number,'27/08/2008')<='01/01/2020'

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-20 : 18:13:58

Try changing the dates:

SELECT DATEADD(dd,7* number, '20080813'),
 DATEADD(dd,7* number, '20080820'),
 DATEADD(dd,7* number, '20080827')
FROM master..spt_values
WHERE type='p'
and DATEADD(dd,7* number, '20080827') <= '20200101'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources