Clarity

I have a request from a user to "concatenate" dates into a single field.  I can format the date as "MM/DD/YY", but I am trying to trim the leading zeros or format the Date with leading zeros to have both formats match.

By default, Clarity displays dates without the leading zeros (1/1/15)

When I have the To_Char(TSK.PRFINISH,'MM/DD/YY'), the date shows as 01/01/15

Is there a way to either display the Clarity "Date" format as 01/01/15?

Or... Is there a way to display the To_Char(date,'MM/DD/YY') as 1/1/15?

REPLACE(To_Char(my_date,'MM'),'0','') || '/' || REPLACE(To_Char(my_date,'DD'),'0','') || '/' || To_Char(my_date,'YY')

(but really, the "Clarity default" format is the date format related to the locale of the logged-in-user, so really your code should be picking up the locale of the @WHERE:PARAM:USER_ID@ and working out what the relevant date format for that user's locale is - I suspect that the system knows about that somewhere, but I have never dug deep enough to work it out (or written my own reusable-code to work it out) - if of course you know that you will only ever use one locale/date-format, then you can ignore this comment! )

Try this: to_char(my_date, 'fmMM/DD/YY')

oh yeah, doh!

(my rambling comment on locales still applies though )

Hi Nick, Can you show me where you found the prefix for the date format?  I have been searching and only found other constructs that were invalid.  That 'fmMM/DD/YY' is great.

Thanks,

Lowell

https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm has it (just an example from Oracle's site)

So first your try the options your database options are (for the date format)

If that does not do it for you then you try what Dave posted. That also works if you have problems with decimals and decimal separator or leading and trailing characters or numbers.