1

I retrieved date from one table in a mm/dd/yyyy hh24:mi:ss format. Now I want to store it in another table in a dd-mon-rr format.

Will any implicit conversion take place? If not, please suggest the query!

Queries used:

select DATETOSTRING(r_date, 'mm/dd/yyyy hh24:mi:ss') as r_date from abc;

insert into efg values(r_date);

Note: Current date format of database is dd-mon-rr.

Antti29
  • 2,953
  • 12
  • 34
  • 36
Pragati Gupta
  • 45
  • 5
  • https://stackoverflow.com/questions/7606745/how-to-change-the-date-format-from-mm-dd-yyyy-to-yyyy-mm-dd-in-pl-sql – Fab Sep 29 '17 at 07:19
  • `ORA-00904: "DATETOSTRING": invalid identifier` – MT0 Sep 29 '17 at 08:04
  • 1
    A date is a date, so store it as a date. A date is internally handled as a numeric format. That allows you to do easy calculations with it, compare it, sort it... When you format it, it's a fixed string that lacks most of those features, and it takes more space. So don't store formatted dates. Formatting is for display only. – GolezTrol Sep 29 '17 at 08:10

2 Answers2

2

Dates (stored in tables) are represented by 7 bytes - they do not have any format associated with them. If they are formatted as a string then that is the client program which you are using to access the database applying its own formatting to the date (which you can usually set via the preferences in that program).

If the "date" is stored with a format then you are not storing it as a date but storing it as a string (i.e. VARCHAR2) format.

DATETOSTRING is not an Oracle function - TO_CHAR( datevalue, format_model, nlsparams ) is an Oracle function.

I retrieved date from one table in a mm/dd/yyyy hh24:mi:ss format. Now I want to store it in another table in a dd-mon-rr format.

No, if you retrieved a DATE then you got 7-bytes from the database - the user interface (i.e. SQL/Plus, SQL Developer, Java, etc.) you are using performed an implicit conversion to make those 7-bytes understandable to you, the user, when it displayed it.

SQL/Plus and SQL developer will use the NLS_DATE_FORMAT, NLS_DATE_LANGUAGE and NLS_TERRITORY session parameters as the default format model and NLS parameters to the TO_CHAR function to perform this implicit conversion - but these are per-user settings and should not be relied upon to be consistent across multiple users (especially in an international setting).

If you want to store a DATE then it has NO format - if you then want to display it with a specific format then you will need to convert it from a DATE to a string using TO_CHAR with your desired format model.

So, you can simply use:

To copy the date from one table to another:

INSERT INTO efg (r_date)
  SELECT r_date FROM abc;

To get the date in your desired format:

SELECT TO_CHAR( r_date, 'dd-mon-rr' ) AS r_date
FROM   efg
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Actually the problem is not as that simple to transfer date from one table to table.In short ,I want to store r_date(a string which is in given date format)retrieved from one database to another Database in dd-mon-rr (current format). – Pragati Gupta Sep 29 '17 at 09:01
  • @PragatiGupta Do **NOT** do that - store the values as a `DATE` and then format it, as required, when you query the database. If necessary, create a virtual column with the formatted value or, to speed things up, add a function-based index on the formatted date but store the underlying value as a `DATE` datatype. – MT0 Sep 29 '17 at 09:05
  • How can I implement this through java?Can I use SimpleDateFormat? – Pragati Gupta Oct 03 '17 at 17:32
  • @PragatiGupta That would be a good question to search StackOverflow for an answer for; and if you can't find an answer then you can ask it as another question. – MT0 Oct 03 '17 at 18:40
1

You don't store date in a specific format, your date is always stored as a number inside the database. The way it is displayed to you depends on your specific client parameters. For exemple, if you do:

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

all your dates will be displayed in this format, that doesn't mean the date will be stored in this format if you insert to a table, the date will still be a number.

Cyrille MODIANO
  • 2,246
  • 2
  • 21
  • 33