SQL Server adding one month to a date represented as an 8 digit decimal

Question

A SQL Server application we use (accpac) represents dates as an 8 digit decimal in ISO format (example: today's date is 20100802)

I need to add one month to this. I've found a way to do it, but there must be a better way. The steps of my solution are:

declare @accpacDate as decimal
set @accpacDate = 20100101

declare @date1 as date
declare @date2 as date

set @date1=cast(CAST(@accpacDate as varchar(8)) as datetime) /*get the starting value as a date */
set @date2=DATEADD(month,1,@date1)

select CONVERT(varchar(8),@date2,112) as aVarchar 
select convert(decimal,CONVERT(varchar(8),@date2,112)) as aDecimal

decimal? as in a number: 20 million, 100k, 802 etc? – gbn Aug 02 '10 at 12:55 — gbn, Aug 02 '10 at 12:55
why using once cast, once convert ? they are the same !? – iDevlop Aug 02 '10 at 13:10 — iDevlop, Aug 02 '10 at 13:10

score 4 · Answer 1 · answered Aug 02 '10 at 13:01

4

SELECT CONVERT(VARCHAR(8),DATEADD(MONTH,1,CONVERT(VARCHAR(8),20100802,112)),112)

answered Aug 02 '10 at 13:01

Hari Menon

33,649
14
85
108

this should be the answer. Not that wes's answer isn't good, this is so simple it's killer. – baash05 Feb 23 '11 at 05:38

score 1 · Accepted Answer · answered Aug 02 '10 at 13:13

It seems about right what you are doing.

String and Date manipulation is pretty core in SQL, no fancy wrappers for auto-converting and manipulating date formats (accpac, memories, shiver).

You could write that into a user function, to add days to a accpac date, and return the result:

create function accpacadd 
(   @accpacdate decimal,
    @days int)
RETURNS decimal
AS BEGIN

declare @date1 as datetime

set @date1=cast(CAST(@accpacDate as varchar(8)) as datetime) /*get the starting value as a date */
set @date1=DATEADD(day, @days, @date1)
return convert(decimal, CONVERT(varchar(8), @date1, 112)) 

END

So then you can just call it with min code:

select dbo.accpacadd(20100102, 5)
select dbo.accpacadd(20100102, -5)

Gives 20100107 and 20091228 respectively

SQL Server adding one month to a date represented as an 8 digit decimal

2 Answers2