Convert Date Field

slgott

Summary

Convert from MM/DD/YYYY to YYYY-Qx

Content

Hello,

I have a date field I want to convert. I want to convert that from MM/DD/YYY to "YYYY-Qx". Any thoughts on how to do this? I've tried using the Calendar functions in the formula. They work, but don't get me the exact above format. I have to use two separate columns. One gets the quarter number so, in the example above, it would just say "1". The other column I'm able to get the year "2020". When I try do a Concat in the 3rd column, I get syntax error b/c I think it doesn't like to concatenate formulas apparently. Any help would be appreciated. 

Thanks,

Stephanie

Christian Berg

Stephanie,

I guess you got "Function Concat does not support non-text types" since you're working with numerical values whereas CONCAT is a string function. So just cast it.

cast(YEAR(NOW()) as char(4)) || ' - Q' || cast(QUARTER_OF_YEAR(NOW()) as char(1))

This gives you what you want for right now. Still - I'd do this in a proper time dimension if you use it a lot since it's always better to do things once centrally rather than X times for every single execution AND every single row that's being parsed.

slgott

The Time dimension works for the current dates. I'm trying to get the quarter of the "Expected Close Date". The Secondary Date dimensions have a Quarter for the Actual Close Date. What I'm trying to do is show the Quarter of the "Expected Close Date" of Open Opportunities. It doesn't appear there is an out of the box dimension for that. If you know of one in Sales Cloud OTBI that I'm not seeing, let me know. Otherwise, your formula worked great by substituting "NOW" for the "Expected Close Date" dimension.. I appreciate your time. Thank you so much. 

Stephanie