oracle update date format mask

Question

I have this procedure, i need birthday field to be in format 16-JUL-2018, but when i run procedure it is updated in 16-JUL-18 format. How to update it in wanted format ?

PROCEDURE update_affiliate (pid                NUMBER,
                                   i_username         VARCHAR2,
                                   i_first_name       VARCHAR2,                              
                                   i_birthday         DATE)
       IS
       BEGIN
          UPDATE   affiliate_v
             SET   username = UPPER (i_username),
                   first_name = i_first_name,
                   birthday = TO_DATE(i_birthday,'DD-MON-YYYY'),
           WHERE   party_id = pid;

       END update_affiliate;

Its not the saving issue, as you think, but the SEEING issue. Well, its not an issue in the first place - the date is shown in format, depending on your NLS_DATE_FORMAT. — Ychdziu, Jul 16 '18 at 09:20
NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET AL32UTF8 NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE This is what i get when run select * fron V$nls_parameters — civesuas_sine, Jul 16 '18 at 09:25
**NEVER**, ever call `to_date()` on a value that is already a date. That will first convert the `date` value to a `varchar` just to convert that `varchar` back to a `date` which it was to begin with. — , Jul 16 '18 at 09:29

MT0 · Accepted Answer · 2018-07-16T09:25:38.443

You do not need to convert i_birthday to a DATE using TO_DATE as it is already a DATE data type. Just use:

PROCEDURE update_affiliate (
  pid                NUMBER,
  i_username         VARCHAR2,
  i_first_name       VARCHAR2,                              
  i_birthday         DATE)
IS
BEGIN
  UPDATE affiliate_v
  SET    username   = UPPER (i_username),
         first_name = i_first_name,
         birthday   = i_birthday,
  WHERE  party_id   = pid;
END update_affiliate;

How to update it in wanted format?

This is a common misconception that dates in the database have a format.

A date does not have a format - it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date is given a format so that you, the user, find it meaningful on the client software.

Since you are passing in a DATE you don't need to do anything.

What you probably want to ask is:

Now I have updated the date, how can I get the user interface to display the date in the format I want when I query the table?

If you are using SQL/Plus or SQL Developer then it will use the NLS_DATE_FORMAT session parameter to format the date. You can change this using:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

Note: This is a session parameter and will only change the format for the current session (not for any other users or any subsequent sessions).

You can also change the preferences in the SQL Developer GUI as described here.

Or you can just use TO_CHAR and set the format you want:

SELECT party_id,
       username,
       first_name,
       TO_CHAR( birthday, 'DD-MON-YYYY' ) AS birthday
FROM   affiliate_v

Yea you are right, my colleague says that is gets in dd-mon-rr format just need to find what function displaying and change it to rrrr format. Thanks on explanation.. — civesuas_sine, Jul 16 '18 at 09:42
@civesuas_sine Unfortunately, your colleague is not quite correct - it does not "get [the date] in [XYZ] format" or in any format - it just gets the 7 unformatted bytes and once it has the unformatted data the user interface you are using (SQL/Plus, SQL Developer, Java, etc) will apply its default format when it tries to display it. Your issue is that you do not want the default format and either need to change the default or, better, use `TO_CHAR` to explicitly set your desired format. — MT0, Jul 16 '18 at 09:49
I m using TOAD and in nls parameters nls_date_format is set to dd-mon-rr.. I can change it there but its only valid for that session. How can i save it to stay that ? — civesuas_sine, Jul 16 '18 at 12:27
@civesuas_sine The best solution is to modify your query to use `TO_CHAR` and explicitly set the format model so that you do not have to modify the session parameters (and so the output is consistent for all users who run the query). An alternate solution would be to set the `NLS_DATE_FORMAT` in a logon trigger (however this does not prevent someone changing the format later in the session and then your output will change). — MT0, Jul 16 '18 at 12:32

oracle update date format mask

1 Answers1