We help IT Professionals succeed at work.

DateDiff dynamic sql command

emi_sastra
emi_sastra asked
on
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.
Comment
Watch Question

View Solution Only

CERTIFIED EXPERT
Commented:
The answer depends on what do you mean dynamic sql command.

If you build SQL statement as the result of string concatenation, like

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

then yes,

If you directly execute this select statement, then no, it should be like the following:

(SELECT dbo.DynamicDateDiff('d', A.TglJatuhTempo, @ToDate)) AS HariJatuhTempo
emi_sastra

Author

Commented:
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.
OMC2000
CERTIFIED EXPERT

Commented:
It must be due to the difference between selected  value of A.TglJatuhTempo and  '2019-02-21' equal to 90 days
emi_sastra

Author

Commented:
My mistake.

Wrong column.

Thank you.
emi_sastra

Author

Commented:
Thank you very much.