Hello everyone -
I need to check to see if a date is in the future, and if it is, leave the result blank. If the date is in the past, then display the date.
Essentially, I'm creating a calculated field that will contain a string (blank character) or a date (which I've needed to cast as a varchar in order to work.)
The cast date field comes through in format yy/mm/dd and I'd like it to be yyyy-mm-dd.
Is this possible?
Thank you very much.
Not sure if I've completely understood, but I'll make a stab and you can let me know if I've got it wrong.
I think you'll probably want something along the lines of:
CASE
When [DateField] > getdate() then NULL
ELSE
cast(Year([DateField]),char(4)) + '-' + cast(month([DateField]),char(2)) + '-' + cast(day([DateField]),char(2))
END
You might need to add some logic in to put leading zeroes where the day or month is less than 10, but you'll need to experiment around that.
C
Chris,
Perfect, thank you. Worked great (will need to play with leading zeros.)
Much appreciated.
In case anyone else needs to do this in the future, here's the code I ultimately ended up using. I'm sure there is room for improvement and would love to hear any thoughts on that.
CASE
WHEN [Date Field]) > GetDate()
THEN NULL
ELSE
CAST(EXTRACT(YEAR, [Date Field]),VARCHAR(4))
|| '-' ||
CASE
WHEN EXTRACT(MONTH, [Date Field]) < 10
THEN
'0' ||
CAST(EXTRACT(MONTH, [Date Field]),VARCHAR(1))
ELSE
CAST(EXTRACT(MONTH, [Date Field]),VARCHAR(2))
END
|| '-' ||
CASE
WHEN EXTRACT(DAY, [Date Field]) < 10
THEN
'0' ||
CAST(EXTRACT(DAY, [Date Field]),VARCHAR(1))
ELSE
CAST(EXTRACT(DAY, [Date Field]),VARCHAR(2))
END
END
I was about to post a new question and stumbled upon this solution. This solution worked perfectly for what I needed! Thanks!!
Just one last thought, if you're going to use that in several reports then push that calculation back into the Framework. And ideally you'd create a Calendar table with all the calculations for each date pre-populated, so that you don't need to do it each time.