asked on
DateDiff dynamic sql command
Hi All,
I have below function.
Is it correct ?
And I want to call it from sub query.
declare @ToDate Date = '20200101'
Is the sub query correct ?
Thank you.
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.
Microsoft SQL Server
Last Comment
emi_sastra
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
It must be due to the difference between selected value of A.TglJatuhTempo and '2019-02-21' equal to 90 days
ASKER
My mistake.
Wrong column.
Thank you.
Wrong column.
Thank you.
ASKER
Thank you very much.
I try below code.
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.