asked on

SQL 2000 / 2005 - increase date by 1

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

hongjun

try this

select @date = DateAdd(d, 1, @date)

hongjun

See this

select DateAdd(d, 1, getDate())

Use DateAdd function

Guy Hengel [angelIII / a3]

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

Guy Hengel [angelIII / a3]

if @DATE is integer:

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

TechMonster

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hongjun

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

TechMonster

ASKER

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

is the key to the code.

Thanks everyone.