twfw123
asked on
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 ?
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 ?
The best way: Not violating 1 NF.
Oops, syntax error:
select convert(nvarchar(6), DATEADD(mm , 1 , cast(myDateField + '01' as datetime)), 112)
What data type has your "date" column?
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.
ASKER
the field is INT
Then you could alter my statement this way:
select cast(convert(nvarchar(6), DATEADD(mm , 1 , cast(myDateField + '01' as datetime)), 112) as integer)
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.
@Lee: You need to convert the INT explicitly to VARCHAR before adding '01'. Otherwise type precedence will come into play.
That's true.
You may need to cater for blank fields in a where clause.
select cast(convert(nvarchar(6), DATEADD(mm , 1 , cast(cast(myDateField as integer) + '01' as datetime)), 112) as integer)You may need to cater for blank fields in a where clause.
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
tedious in the extreme
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 = 201601tedious in the extreme
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sanity prevails. use Scott's
ASKER
thanks for the assistance, Paul yes Scott offers a very nice method...
Open in new window