Number of days between yesterday and due date

[Anonymous]

hi,

Am using MS Analysis Server and need to calculate the number of days between yesterday and the due date. For yesterday i used the GetDate()-1 formula and i get a date like 2023-01-01 00:00:00:000 the due date looks similar. My script that calculates the number of days looks like this:

CASE WHEN TRY_CONVERT(DATE, CONVERT(VARCHAR(8), [DueDate]), 112) IS NULL OR TRY_CONVERT(DATE,CONVERT(VARCHAR(8), [CurrentDate]), 112) IS NULL

THEN 0

ELSE DATEDIFF(dd, TRY_CONVERT(DATE, CONVERT(VARCHAR(8), [DueDate]),112, TRY_CONVERT(DATE, CONVERT(VARCHAR(8), [CurrentDate],112))

END

now, the script is not working and gives an error message. Tried a lot of alternatives but nothing worked so far.

Anyone an idea what needs to be done???

[Anonymous]

Hi Mahesh, thanks for the swift response, unfortunately your suggestion gave issues as well. We are trying now "SWITCH(TRUE(), ISBLANK([DueDate]) || ISBLANK(TODAY()-1), 0, TRUE(), DATEDIFF([DueDate], TODAY()-1, DAY))"

the CurrentDate which is in fact GetDate()-1 seems to be the problem....

[Mahesh0016]

@Anonymous Please Try below script
CASE WHEN CONVERT(DATE,CONVERT(VARCHAR(20), [DueDate], 109),112) IS NULL OR CONVERT(DATE,CONVERT(VARCHAR(20), [CurrentDate], 109),112) IS NULL

THEN 0

ELSE DATEDIFF(dd, CONVERT(DATE,CONVERT(VARCHAR(20), [DueDate], 109),112), CONVERT(DATE,CONVERT(VARCHAR(20), [CurrentDate], 109),112))

END