1

I have a date that looks like this: 29-DEC-2020 00:00
I want to format it to dd-mon like this: 29-DEC
How do I convert this without doing it with to_char()?

tycho de vink
  • 41
  • 5
  • 4
    Dates are not stored in any format in the Oracle database. they are stored in internal binary format. whatever you see in the tool that you are using are converted to varchar using `NLS_DATE_FORMAT`. If you just want to see dates in your own format then use the `alter session ` command. -- `alter session set NLS_DATE_FORMAT = 'dd-mon'` – Popeye Jan 29 '21 at 08:39
  • How would I apply this in apex? – tycho de vink Jan 29 '21 at 08:44
  • https://stackoverflow.com/questions/58469126/alter-session-set-nls-date-format-doesnt-work-in-apex-however-works-in-sql-dev – Popeye Jan 29 '21 at 08:49
  • Where in apex do you want to do this ? Is this a form, a report, do you want all dates in the application to have this format ? Please update your question to contain as much info as possible. – Koen Lostrie Jan 29 '21 at 09:01
  • I have a linechart where I need to apply this. – tycho de vink Jan 29 '21 at 10:37

2 Answers2

0

In apex, you can set the date format for your application in multiple places

  • For the entire app, go to "Application Definition Attributes" (1st entry in shared components) > "Globalization". There you have the option to choose the format mask for all different date datatypes. This will effect every occurence of this datatype in your application.
  • For a specific page item or for a report column, you can set the format mask under "Appearance" > "Format Mask"
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19
  • I need to apply this for specifically a linechart – tycho de vink Jan 29 '21 at 10:42
  • In that case I think you'll have to do TO_CHAR – Koen Lostrie Jan 29 '21 at 11:01
  • Yeah I want 2 lines in the linechart but apex forces you to order them again so when you've already converted it to a character it orders them all wrong. – tycho de vink Jan 29 '21 at 12:15
  • It's really hard to answer your question when you keep adding additional info *after* the answer... Maybe you could update the initial question, add some sample data that makes it possible for someone to reproduce the case. Again, add as much info as possible. – Koen Lostrie Jan 29 '21 at 12:42
0

If your "date" looks like 29-DEC-2020 00:00, then it doesn't look like a date to me. It looks like a string.

So, you can use string functions:

substr(datecol, 6)

Of course, if it is really a date, you should either use to_char() or set the parameters to ignore the year in the UI. However, I strongly discourage you from doing that.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786