CODEWITHSUNDEEP
sqloracle-databasedate
ahmed
ahmed

Reputation: 45

Convert 'DD-MM-YY' to 'Month Year' oracle

I have a date a field that displays date in the format '19-JAN-20' I would like to display it as 'January 2019' using oracle database. 

select '19-JAN-19' FROM DUAL

Upvotes: 0

Views: 1036

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

Looks like a nested TO_CHAR, if "date" value you mentioned - 19-JAN-20 is stored as a string:

SQL> with test (col) as
  2    (select '19-JAN-20' from dual)
  3  select
  4    to_char(
  5      to_date(col, 'dd-mon-yy', 'nls_date_language = english'),
  6      'fmMonth yyyy', 'nls_date_language = english'
  7      ) result
  8  from test;

RESULT
--------------
January 2020

SQL>

If your database speaks English, you can omit the NLS_DATE_LANGUAGE part (mine speaks Croatian so I included it).

However, if it is a date you'd just want to display differently, then:

(Just to avoid NLS_DATE_LANGUAGE in TO_DATE):

SQL> alter session set nls_date_language = 'english';

Session altered.

Default format in my database:

SQL> select sysdate from dual;

SYSDATE
--------
08.06.20

Alter session to desired format:

SQL> alter session set nls_date_format = 'fmMonth yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------
June 2020

Or, apply TO_DATE function with desired format mask:

SQL> select to_char(sysdate, 'fmMonth yyyy') result from dual;

RESULT
--------------
June 2020

SQL>

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

You need to use TO_CHAR to display in the required format:

select to_char(DATE '2019-01-20', 'Month YYYY') dt from dual;

DT           
-------------
January  2019

select '19-JAN-19' FROM DUAL

There are couple of issues with that statement:

  1. '19-JAN-19' is a string, not a date. Always use TO_DATE to explicitly convert it to date, or stick to ANSI date literal like I used in my demo.

  2. Do not use two-digit YY representation for year, that's the reason Y2K bug was introduced. Always use YYYY.

Upvotes: 3

Related Questions