The best way to add next month

Question

Post reply

The best way to add next month

nguyennd

SSCommitted

Points: 1881
More actions
April 15, 2010 at 8:03 pm

#223321

Please look at the follow code:
- The variable @Date has the value is 201012 (yyyymm)
- Now, I want to increse the month(mm) in the @Date variable to 1
I had done this thing by 2 ways as the following code:
DECLARE @Date int = 201012
-- way 1
SELECT CONVERT(INT,SUBSTRING(CONVERT(CHAR(8),DATEADD(MONTH,1,Convert(Date,CONVERT(char(8),CONVERT(CHAR(6),@Date)+'01'),8)),112),1,6))
-- way 2
SELECT CASE WHEN CONVERT(INT,SUBSTRING(CONVERT(CHAR(6),@Date),5,2))>11 THEN @Date+89 ELSE @Date + 1 End
Do you have any good way which is better than 2 ways above ?:-D
http://unitech.vn

Viewing 15 posts - 1 through 14 (of 14 total)

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

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 1

U can try this

SELECT

CASE WHEN (@Date % 100) = 12 THEN

((@Date / 100) + 1) * 100 + 1

ELSE

(@Date / 100) * 100 + (@Date % 100) + 1

END

Eralper

SQL Server T-SQL

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

nguyennd SSCommitted Points: 1881 More actions · Answer 2

:w00t: oh, good SQL. I'm rewrite your SQL and...

SELECT

CASE WHEN (@Date % 100) = 12 THEN

@Date + 89

ELSE

@Date + 1

END

Thanks for your support, 😀

Any one else, huhu.

http://unitech.vn

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 3

Hi nguyennd,

You are genius 🙂

I was so detailed with mod, division, etc.

You made the query more simple.

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

LutzM SSC Guru Points: 107049 More actions · Answer 4

The best way to deal with date values is to store it as date values.

So, instead of having an integer value it woul be a lot better and easier to store it as datetime or date (if you're on SS2K8).

Just my 2 cents...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Just another option:

select convert(char(6), dateadd(mm, 1, dateadd(mm, right(@Date, 2) - 1, dateadd(yy, cast(left(@Date,4) as int) - 1900, 0))), 112)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

select

*,

NextMonth =

convert(char(6),dateadd(mm,(((DT/100)-1900)*12)+(Dt%100),0),112)

from

( -- Test Data

select DT = 201011 union all

select DT = 201012 union all

select DT = 201101

) a

Results:

DT NextMonth

----------- ---------

201011 201012

201012 201101

201101 201102

(3 row(s) affected)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

nguyennd (4/16/2010)
:w00t: oh, good SQL. I'm rewrite your SQL and...
SELECT
CASE WHEN (@Date % 100) = 12 THEN
@Date + 89
ELSE
@Date + 1
END
Thanks for your support, 😀
Any one else, huhu.

Shorter?

select @Date+case when @date%100=12 then 89 else 1 end

Paul White SSC Guru Points: 150467 More actions · Answer 8

Michael Valentine Jones (4/16/2010)
Shorter?
select @Date+case when @date%100=12 then 89 else 1 end

Even shorter?

SELECT @Date+1+88*((@date%100)/12)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vmssanthosh SSC Eights! Points: 856 More actions · Answer 9

hi

This is my way..

declare @date varchar(100)='201012'

select convert(varchar(6),dateadd(M,1,convert(date,@date+'01')),112)

Paul White SSC Guru Points: 150467 More actions · Answer 10

vmssanthosh (4/18/2010)
hi
This is my way..
declare @date varchar(100)='201012'
select convert(varchar(6),dateadd(M,1,convert(date,@date+'01')),112)

That's fine - but why use VARCHAR for fixed-length data? 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

lmu92 (4/16/2010)
The best way to deal with date values is to store it as date values.
So, instead of having an integer value it woul be a lot better and easier to store it as datetime or date (if you're on SS2K8).
Just my 2 cents...

Ditto. I'll also add that since we're adding months one at a time, there's probably a cursor or While Loop involved somewhere here. 😉

--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)

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 12

🙂

After Michael, Paul has the shortest statement ever ... Great!

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

Paul White NZ (4/18/2010)
Michael Valentine Jones (4/16/2010)
Shorter?
select @Date+case when @date%100=12 then 89 else 1 end
Even shorter?
SELECT @Date+1+88*((@date%100)/12)

:Whistling:

SELECT @Date+1+88*(@date%100/12)

Paul White SSC Guru Points: 150467 More actions · Answer 14

Michael Valentine Jones (4/19/2010)
:Whistling:
SELECT @Date+1+88*(@date%100/12)

:laugh: :laugh: :laugh: :laugh: :laugh:

Nice one.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi