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

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

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

Solution

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