How can I do a query that return ’01-JAN-1900’

Question

We are using Oracle 11. Both below queries return 01-JAN-00

  SELECT to_date('01-JAN-1900','dd-MM-yyyy') FROM DUAL
  SELECT to_date('01-JAN-1900','dd-MM-RRRR') FROM DUAL

How can I do a query that return ’01-JAN-1900’ ?

I need to do the below query (myDtColumn is a Date column), if I just use '01-JAN-1900' without the to_date, I will get an error 'inconsistent data types: expected %s got %s'

case when myDtColumn is null then to_date('01-JAN-1900','dd-MM-yyyy') else 
myDtColumn  end as myDt

When I try the below, I am still getting '01-JAN-00' instead of '01-JAN-1900'

case when myDtColumn is null then DATE '1900-01-01' else 
myDtColumn  end as myDt

I don't think this is a query issue, I think you're getting the right dates, they're just displaying with 2 digit years instead of 4. You probably need to look at the settings for whatever query editor or tool you're using to run the queries. You could of course convert to string with specific date format, but that seems to defeat the purpose, and you might as well just select string as @GordonLinoff says. What are you doing this for? Is this part of a bigger query? — Ben, Jan 17 '19 at 22:06
Yes, it is part of a bigger query. I just edited my question above — faujong, Jan 17 '19 at 22:10
Format `MM` means months **number**, not months name. Use either `to_date('01-01-1900','dd-MM-yyyy')` or `to_date('01-JAN-1900','dd-MON-yyyy', 'nls_date_language = american')` — Wernfried Domscheit, Jan 18 '19 at 06:46

MT0 · Accepted Answer · 2019-01-17T22:16:22.080

Dates do not have a format - they are represented internally by 7 or 8 bytes. If you want a date to have a format then you will need to convert it to a data type that can be formatted (i.e. a string). When the user interface you are using needs to display a date then it will implicitly convert it to a string and Oracle uses the NLS_DATE_FORMAT session parameter as its default format.

You can change this using:

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

Then either of your queries or, using a date literal:

SELECT DATE '1900-01-01' FROM DUAL

Will be implicitly formatted using the NLS_DATE_FORMAT when the UI displays them.

Or for your query:

SELECT COALESCE( myDtColumn, DATE '1900-01-01' ) as myDt
FROM   your_table;

If you do not want to change the session parameters then use TO_CHAR( date_value, 'DD-MON-YYYY' ):

SELECT TO_CHAR( COALESCE( myDtColumn, DATE '1900-01-01' ), 'DD-MON-YYYY' ) as myDt
FROM   your_table;

How can I do a query that return ’01-JAN-1900’

1 Answers1