Date Format When Casting a Date to String (VarChar)

[dmburke11]

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.

[BigChris]

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

[dmburke11]

Chris,

Perfect, thank you. Worked great (will need to play with leading zeros.)

Much appreciated.

[dmburke11]

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

[msawyer]

I was about to post a new question and stumbled upon this solution.  This solution worked perfectly for what I needed!  Thanks!!

[BigChris]

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.