0

I have inserted into a table in Oracle. My implementation without PLSQL would be:

SELECT to_date('1900-01-01','YYYY-MM-DD') + (rownum - 1) AS DT_CAL, 
       rownum AS NUM_JOUR 
FROM   dual 
CONNECT BY to_date('1900-01-01','YYYY-MM-DD') + (rownum - 1) <= 
                                                      to_date('2000-12-31','YYYY-MM-DD')

result is: 05/28/1900, not 1900-05-28. Can you help me understand what the problem is?

Araour Kahy
  • 1
  • 1
  • 7

2 Answers2

1

The DATE data type does not have a format; Oracle stores it as either 7- or 8-bytes and it is not until it is passed to a client program (i.e. SQL/Plus, SQL Developer, Toad, Java, Python, etc) and that client program formats it according to whatever rules it has that the date gets a format.

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 = 'YYYY-MM-DD HH24:MI:SS';

(Be aware that this will only change the format in the current session and will not change it for any other sessions/users.)

If you want to give the date a particular format then you will need to convert it to a string.

MT0
  • 143,790
  • 11
  • 59
  • 117
0

to_date() takes your string parameter, matches it to the format you provide in the second parameter, and constructs a date field from it. The date field isn't using the format you provided in the second parameter - in fact it'll be stored using some internal data representation that has no format at all (a number, in all likelihood).

To present a format back out in the results from a date field, you can either:

  1. Have the client executing the query set the NLS parameters (at session level) to provide a localized format, with an ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'; statement), or
  2. Use to_char(..., 'YYYY-MM-DD') around your existing field to turn the date back into a string formatted the way you want to have it. Where you replace ... with your current column definition in the select.

Approach #1 is already happening, as there'll already be an NLS_DATE_FORMAT set that is producing the current format, but it's with a format you don't want, so if you can control it and change it there, you can do it that way. If you can't and you must have the format a single consistent other way, then #2 could be the way to go.

topdog
  • 377
  • 1
  • 7
  • 1
    This is the correct answer. For "production" queries, it is always best to use to_char(DATE_COLUMN, '.....') with the desired date format model, since we can't control another user's NLS settings; but for the OP just checking results for him/herself, the NLS_DATE_FORMAT solution suffices. NOTE regarding the internal format Oracle uses for internal storing of dates: "BluShadow" at OTN has a good explanation. To find out for yourself, you can run SELECT DUMP(SYSDATE) FROM DUAL and try to understand it. It's not obvious - but it is not a number, it's seven numbers. –  Jul 23 '16 at 22:47
  • 1
    @mathguy The format of a date is also on [Stackoverflow documentation](http://stackoverflow.com/documentation/oracle/2087/dates/6848/the-format-of-a-date#t=201607240002226712408). – MT0 Jul 24 '16 at 00:23
  • @MT0 Excellent, thank you! I saw the ads for SO documentation but didn't bother to look into it yet. Great idea, likely to join the effort soon. –  Jul 24 '16 at 00:36