Link to home
Create AccountLog in
Avatar of emi_sastra
emi_sastra

asked on

DateDiff dynamic sql command

Hi All,

I have below function.


 
ALTER Function dbo.DynamicDateDiff
  (@Interval VarChar(2),
   @Date1 DateTime,
   @Date2 DateTime)
Returns Int
As
Begin
  Return (Select Case @Interval
                   When 'w' Then DateDiff(WEEK, @Date1, @Date2)
                   When 'y' Then DateDiff(YEAR, @Date1, @Date2)
                           When 'd' Then DateDiff(DAY, @Date1, @Date2)
                            When 'm' Then DateDiff(MONTH , @Date1, @Date2)
                   End
         )
End


Is it correct ?

And I want to call it from sub query.

declare @ToDate Date = '20200101'



select a.Item
 , (SELECT DBO.DynamicDateDiff(''d'', A.TglJatuhTempo, ''' + @ToDate + ''')) AS HariJatuhTempo
from ItemMaster a


Is the sub query correct ?

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of OMC2000
OMC2000
Flag of Russian Federation image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of emi_sastra
emi_sastra

ASKER

Hi Omc2000,

I try below code.


DECLARE @TODATE CHAR(8) = '20190221'

DECLARE @sql VARCHAR(MAX) = 'select TOP 1 NomorFkt, tglfkt
 , (SELECT DBO.DynamicDateDiff(''d'', A.TglJatuhTempo, ''' + @ToDate + ''')) AS HariJatuhTempo
from THINVSELL a'

print @sql
execute (@sql)

select TOP 1 NomorFkt, tglfkt
 , (SELECT DBO.DynamicDateDiff('d', A.TglJatuhTempo, '20190221')) AS HariJatuhTempo
from THINVSELL a
NomorFkt                       tglfkt                  HariJatuhTempo
------------------------------ ----------------------- --------------
NRJ-AU-2019-02-0024            2019-02-21 00:00:00.000 -90

(1 row(s) affected)

Why the result is  minus 90 ?

Thank you.
It must be due to the difference between selected  value of A.TglJatuhTempo and  '2019-02-21' equal to 90 days
My mistake.

Wrong column.

Thank you.
Thank you very much.