Auto Increment Date - 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

Auto Increment Date

Author

Topic

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-01 : 09:45:08

Hi,

I am trying to create a stored procedure which will take 2 parameter.
A start date(08/25/2009) and end date(08/30/2009)

I have a table with 1 column, DATE.
Is it possible for me to auto increment the dates using a stored procedure?

I have read articles on auto incrementing primary key, numbers etc, but can we do it for dates?

So i can see the rows, 08/25/2009, 08/26/2009 so till 08/30/2009.

Please advice.
Padhma

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 09:51:10

declare @start_date datetime, @end_date datetime
select @start_date='08/25/2009',@end_date ='08/30/2009'
select DATEADD(day,number,@start_date) from master..spt_values
where type='p' and number between 0 and DATEDIFF(day,@start_date,@end_date)

Madhivanan

Failing to plan is Planning to fail

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-01 : 09:56:54

Thank you for your response Madhi.
But I have a table by name Test2.
I have some values date values in it already. The procedure should update and replace the existing dates.
Table Test2 has one column DATE.

I am sorry if i am missing to understand your response.
Please advise.
Padhma

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 10:00:18

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-01 : 10:13:31

I have Table Test2 with one column and 5 rows.
08/25/2009
08/26/2009
08/27/2009
08/28/2009
08/29/2009

Now i dont want to manually go and update these dates but i want to create a SP which take startdate and end date from user.

so for example, if user says
exec spAutoIncrementDate '09/01/2009', '09/10/2009'
here number the range is 10 days, later user can give for 5 days also..its should be flexible.

So the output i want here it, to replace the previous dates and fill the table with
09/01/2009
09/02/2009
09/03/2009
09/04/2009
09/05/2009
09/06/2009
09/07/2009
09/08/2009
09/09/2009
09/10/2009

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 10:30:35

delete from table
declare @start_date datetime, @end_date datetime
select @start_date='08/25/2009',@end_date ='08/30/2009'

insert into table(date_col)
select DATEADD(day,number,@start_date) from master..spt_values
where type='p' and number between 0 and DATEDIFF(day,@start_date,@end_date)

select date_col from table

Madhivanan

Failing to plan is Planning to fail

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-01 : 10:49:31

Thank you Madhi.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 11:01:20

quote:
Originally posted by padhma.p

Thank you Madhi.

You are welcome

Madhivanan

Failing to plan is Planning to fail

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-01 : 11:08:35

Madhi, i have the following SP, when i run it it keeps giving me syntax error in line 2 near declare.

alter procedure spAutoIncrementDate
declare @start_date varchar(50)
declare @end_date varchar(50)

begin
delete from Test4

insert into Test4(date)
select DATEADD(day,number,@start_date) from master..spt_values
where type='p' and number between 0 and DATEDIFF(day,@start_date,@end_date)

update Test4
SET date = convert(varchar, convert(datetime, date), 101)

end

I am unable to figure out what is the syntax error.
Any suggestion would be helpful.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-01 : 11:12:50

alter procedure spAutoIncrementDate
as
declare @start_date varchar(50)
declare @end_date varchar(50)

begin
delete from Test4

insert into Test4(date)
select DATEADD(day,number,@start_date) from master..spt_values
where type='p' and number between 0 and DATEDIFF(day,@start_date,@end_date)

update Test4
SET date = convert(varchar, convert(datetime, date), 101)

end

Madhivanan

Failing to plan is Planning to fail

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-01 : 11:17:03

I tried to same, it wont work untill i asked you. By the way madhi, the @paramter is for IN input value's isnt it. So when i run this procedure it should be asking me for the start and end date but it does not. It runs as if it has no parameters for the users to input.

Padhma

padhma.p
Yak Posting Veteran

91 Posts

Posted - 2009-09-01 : 11:47:41

I am all set now. Thank you madhi. I figured it out.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 02:56:49

quote:
Originally posted by padhma.p

I am all set now. Thank you madhi. I figured it out.

Ok. Fine

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources