I'm trying to store date type data from Oracle FORMS with format mask as like DD-MM-YYYY but every time it store as like DD/MON/YY. I already alter session with NLS_DATE_FORMAT, but result is as same as before.

CodePudding user response：

Oracle internal date format that is written in the table is something you can't change in any way, but, in the same time, it is irrelevant. If you are dealing with DATE type column then you should know that it containes both the date and the time. How, where and when you will show it or use it is on you. Here is a sample of a few formats derived from that original Oracle DATE format...

WITH 
    t AS
        (
            Select SYSDATE "MY_DATE_COLUMN" From Dual
        )
Select
    MY_DATE_COLUMN "DATE_DEFAULT_FORMAT",
    To_Char(MY_DATE_COLUMN, 'mm-dd-yyyy') "DATE_1",
    To_Char(MY_DATE_COLUMN, 'yyyy/mm/dd') "DATE_2",
    To_Char(MY_DATE_COLUMN, 'dd.mm.yyyy') "DATE_3",
    To_Char(MY_DATE_COLUMN, 'dd.mm.yyyy hh24:mi:ss') "DATE_4"
From t

You can find a lot more about the theme at https://www.oracletutorial.com/oracle-basics/oracle-date/
Regards...

CodePudding user response：

In Oracle, a DATE is a binary data-type consisting of 7-bytes (representing century, year-of-century, month, day, hour, minute and second). It ALWAYS has those 7 components and it is NEVER stored in any particular human-readable format.

every time it store as like DD/MON/YY.

As already mentioned, no, it does not store a date like that; the database stores dates as 7 bytes.

What you are seeing is that the client application, that you are using to connect to the database, is receiving the 7-byte binary date value and is choosing to convert it to something that is more easily comprehensible to you, the user, and is defaulting to converting the date to a string with the format DD/MON/RR.

What you should be doing is changing how the dates are displayed by the client application by either:

• Change the settings in the Toad (View > Toad Options > Data Grids > Data and set the Date Format option) and allow Toad to implicitly format the string; or
• Use TO_CHAR to explicitly format the date (TO_CHAR(column_name, 'DD-MM-YYYY')).

I'm trying to store data as like DD-MM-YYYY.

If you want to store a date then STORE it as a date (which has no format) and format it when you DISPLAY it.

If you have a valid business case to store it with a format then you will need to store it as a string, rather than as a date, because you can format strings; however, this is generally considered bad practice and should be avoided.

CodePudding user response：

Sadman, to add to what others have posted I suggest you do not write your applications with reliance on the NLS_DATE_FORMAT parameter but rather you screens and application should specify the expected DATE entry format and the code should use the TO_DATE function to store the data into the database. All application SQL should use the TO_CHAR function to format date output for display.