CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Dom Vito
Dom Vito

Reputation: 567

Converting Date to day-mon-year format in Oracle

I need to convert dates like this:

3/2/2016 12:00:00 AM

to this:

2-MAR-2016

Upvotes: 0

Views: 2200

Answers (2)

Richard Hamilton
Richard Hamilton

Reputation: 26434

Oracle's default date format is YYYY-mm-dd. We can use the TO_CHAR method to convert to a specific format.

TO_CHAR(date, 'FMDD-MON-YYYY')

Breakdown

FMDD- Apperantly, just using DD as recommended in the documentation does not format days with a leading 0. You need to use FMDD.

MON- Abbreviated month name

%YYYY- Long year format

Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm

In my-sql, the same could be accomplished with the DATE_FORMAT method

DATE_FORMAT(date, '%d-%b-%y')

Slightly different formatter options

Scroll down to the Datetime Format Elements

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

For ORACLE You can use to_char(your_date, format)

 SELECT TO_CHAR(your_Date ,'DD-MON-YYYY') 
 FROM DUAL;

for mysql 

  SELECT TO_CHAR(your_Date ,'%d-%m-%Y') 
 FROM DUAL;

Upvotes: 2

Related Questions