SL Server 2008 yyyymm +1

Hi - I have a question that I would like assistance with - Im sure this will be a quick one but i keep missing at the moment...
What is the best way to add a month to a YYYYMM dataset in a sql server table.
for example I have a set of dates
201501
201502
that I would like to change to the following
201501  to 201502
201502  to 201503
what would be the best method to achieve this ?
twfw123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
select convert(nvarchar(6), DATEADD(mm , 1 , cast(myDateField + '01')), 112)

Open in new window

0
ste5anSenior DeveloperCommented:
The best way: Not violating 1 NF.
0
Lee SavidgeCommented:
Oops, syntax error:

select convert(nvarchar(6), DATEADD(mm , 1 , cast(myDateField + '01' as datetime)), 112)

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

ste5anSenior DeveloperCommented:
What data type has your "date" column?
0
Lee SavidgeCommented:
I would have assumed that if the format is YYYYMM then it is likely to be a nvarchar() field. It could be an integer I suppose, but my conversion leaves it as a nvarchar(6) field.
0
twfw123Author Commented:
the field is INT
0
Lee SavidgeCommented:
Then you could alter my statement this way:

select cast(convert(nvarchar(6), DATEADD(mm , 1 , cast(myDateField + '01' as datetime)), 112) as integer)

Open in new window

0
Lee SavidgeCommented:
With my way, you are making SQL determine the correct date by treating it as a date, and then converting back to a 6 char string in the form, YYYYMM, and finally to an integer which will give you what you need.
0
ste5anSenior DeveloperCommented:
@Lee: You need to convert the INT explicitly to VARCHAR before adding '01'. Otherwise type precedence will come into play.
0
Lee SavidgeCommented:
That's true.

select cast(convert(nvarchar(6), DATEADD(mm , 1 , cast(cast(myDateField as integer) + '01' as datetime)), 112) as integer)

Open in new window


You may need to cater for blank fields in a where clause.
0
PortletPaulfreelancerCommented:
first explicitly convert the int value to varchar so you can add the string '01'
       convert(varchar(6),@val) + '01'
convert that  (YYYYMMDD string) to date
add 1 month to that date
then convert to 'YYYYMM' string (6 chars)
convert that string to int

then: take a breath, repeat

declare @val int = 201512

set @val = convert(int,convert(varchar(6),dateadd(month,1,convert(date,convert(varchar(6),@val) + '01')),112))

-- result = 201601

Open in new window


tedious in the extreme
0
Scott PletcherSenior DBACommented:
No conversion is needed, so leave the value as an int, since any conversion will add overhead:

[YYYYMM + 1] = YYYYMM + CASE WHEN YYYYMM % 100 = 12 THEN 89 ELSE 1 END

For example:

SELECT YYYYMM, [YYYYMM + 1] = YYYYMM + CASE WHEN YYYYMM % 100 = 12 THEN 89 ELSE 1 END
FROM (
    SELECT 201501 AS YYYYMM UNION ALL
    SELECT 201504 UNION ALL
    SELECT 201512
) AS test_data
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
sanity prevails. use Scott's
0
twfw123Author Commented:
thanks for the assistance, Paul yes Scott offers a very nice method...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.