?
Solved

SQL 2000 / 2005 - increase date by 1

Posted on 2006-06-06
8
Medium Priority
?
507 Views
Last Modified: 2008-02-01
Ok,  Here is another challenging one.

I have set a variable to a specific date and formated it to look like:

20060531

What I need to do now is increase it by one.

I have

 @date = @date + 1

this of course gives

20060532 which is an invalid date.

i am looking for a way to increase it so it will recongize the next month.
This will have to be dynamic of course.  Recongizing each additional month once it arrives.  

20060601.  

Thanks
0
Comment
Question by:TechMonster
  • 3
  • 3
  • 2
8 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 16842879
try this

select @date = DateAdd(d, 1, @date)
0
 
LVL 33

Expert Comment

by:hongjun
ID: 16842887
See this

    select DateAdd(d, 1, getDate())

Use DateAdd function
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16842893
select convert(varchar(8), dateadd(day,1, convert(datetime, @date , 112) , 112 )
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16842907
if  @DATE is integer:

select cast( convert(varchar(8), dateadd(day,1, convert(datetime, cast(@date as varchar(8)), 112) , 112 ) as int)
0
 

Author Comment

by:TechMonster
ID: 16843226
hongjun,

Your code does increase the date by 1 but puts into a different format than what I am looking for.
@date = DateAdd(d, 1, @date)
Results:  Jun 1 2
Which is June 1, 2006.  

My @date is only 8 char long.

angelIII

Your code
convert(varchar(8), dateadd(day,1, convert(datetime, @date , 112) , 112 )
produces an error " The dateadd function requires 3 argument(s)."

I think the code is what I am looking for though.  There needs to be some way of coverting it to the format as you listed 112.

THe challange here is to  figure out the syntex.


0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16843251
I see, missing a bracket:
convert(varchar(8), dateadd(day,1, convert(datetime, @date , 112)) , 112 )

resp:
select cast( convert(varchar(8), dateadd(day,1, convert(datetime, cast(@date as varchar(8))), 112) , 112 ) as int)
0
 
LVL 33

Expert Comment

by:hongjun
ID: 16843264
Try this

declare @date varchar(8)
select @date = 20060531

select convert(varchar(8), dateadd(day,1, convert(datetime, @date , 112)) , 112)

PLEASE AWARD POINTS TO angelIII

hongjun
0
 

Author Comment

by:TechMonster
ID: 16843316
convert(varchar(8), dateadd(day,1, convert(datetime, @date , 112)) , 112)

is the key to the code.

Thanks everyone.

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question