CODEWITHSUNDEEP
sqlt-sqldatemath
Gidil
Gidil

Reputation: 4137

Dateadd without using function

We keep our dates in the DWH in int format. I would like to do some simple Dateadd calculations (adding and subtract months from a date) without having to convert to date and convert back to int.

I have been able to reach something closing to working using 2 methods:
1. Using Modulo and Absolute functions: 

DECLARE @EffectiveDate INT = 20121003, 
        @Diff          INT = -12 

SELECT @EffectiveDate + ( Abs(@Diff) / @diff ) * ( Abs(@Diff) + mnt ) / 12 * 
                        10000 + ( 
              Abs(@Diff) / @diff ) * ( ( Abs(@Diff) + mnt - 1 )%12 - mnt + 1 ) * 
       100 
FROM   (SELECT @EffectiveDate / 100%100 Mnt)T

2.By calculating the year in months, adding/subtracting the required months and dividing by 12: 

DECLARE @EffectiveDate INT = 20121003, 
        @Diff          INT = -12 

SELECT ( yr * 12 + mnt + @Diff ) / 12 * 10000 + 
       ( yr * 12 + mnt + @Diff )%12 * 100 + 1 
FROM   (SELECT @EffectiveDate / 100%100 Mnt, 
               @EffectiveDate / 10000   Yr)T

In both cases I come up with the same problem, December is represented incorrectly as 0 and effects the year outcome as well.

Any ideas?

Upvotes: 0

Views: 100

Answers (1)

dnoeth
dnoeth

Reputation: 60462

Your logic is wrong:

SELECT  ( yr * 12 + mnt + @Diff - 1) / 12 * 10000 + 
       (( yr * 12 + mnt + @Diff - 1) % 12 + 1) * 100 + 1 
FROM   (SELECT @EffectiveDate / 100%100 Mnt, 
               @EffectiveDate / 10000   Yr)T

Of course everything would be much easier if your store dates in DATEs :)

Upvotes: 2

Related Questions