How to convert oracle date format

The date type stored in the Oracle database can be formatted using the to_char() and to_date() functions. This article will introduce the detailed usage and examples of Oracle date format conversion.

1. to_char() function

to_char() function can convert date data type into character data type, the format is as follows:

to_char( date,'format')

where date refers to the date data type, and format refers to the date format template. The following are some commonly used date format templates:

Template                                   Example  
YYYY Four years 2022
YY two years 22
MM month (01-12) 03
Mon month abbreviation MAR
DD date (01-31) 05
Day week FRIDAY
DY Abbreviation of day of the week FRI
HH Hours (00-12) 09
HH24 Hours (00-24) 18
MI Minutes 4 5
SS                       30

The following are some examples of using the to_char() function:

Example 1: Convert date data to character data and display the year, month and day format

SELECT to_char(sysdate,'YYYY- MM-DD') AS "Current date" FROM dual;

Example 2: Convert date data to character data and display the abbreviation of the current month

SELECT to_char(sysdate,' MON') AS "Current month" FROM dual;

Example 3: Convert date data to character data and display the abbreviation of the day of the week of the current date

SELECT to_char(sysdate,' DY') AS "Day of the week" FROM dual;

2. to_date() function

to_date() function can convert character data type into date data type, The format is as follows:

to_data(string,'format')

where string refers to the character data type and format refers to the date format template. The following are some commonly used date format templates:

Template                                                                                                                                                                                                         03
MON Month abbreviation MAR
DD Date (01-31 ) 05
HH Hours (00-12) 09
HH24 Hours (00-24) 18
MI Minutes 45
SS Seconds          30

The following are some to_date() functions Usage examples:

Example 1: Convert character data to date data in the format of year, month and day

SELECT to_date('2022-03-05','YYYY-MM-DD ') AS "Current date" FROM dual;

Example 2: Convert character data to date data in the format of month abbreviation

SELECT to_date('MAR','MON') AS "Current month" FROM dual;

Example 3: Convert character data to date data in the format of hours and minutes

SELECT to_date('09:45','HH24:MI ') AS "time" FROM dual;

The above are the detailed usage and examples of Oracle date format conversion. Through the to_char() and to_date() functions, you can easily implement date format conversion to meet different needs. business needs.

The above is the detailed content of How to convert oracle date format. For more information, please follow other related articles on the PHP Chinese website!