Oracle acquisition of system date and date conversion functions

The following articles mainly introduce the actual application of Oracle to obtain the system date and date conversion functions, as well as how to correctly obtain the system date: SYSDATE (), and format the date: TO_CHAR (SYSDATE (), 'yy/MM/DD HH24: MI: SS.

Or TO_DATE (SYSDATE (), 'yy/MM/DD HH24: MI: SS)

Format the number: TO_NUMBER

Note: TO_CHAR converts the date or number obtained by Oracle to a string.

TO_CHAR (number, 'format ')

TO_CHAR (salary, '$99,999.99 ')

TO_CHAR (date, 'format ')

TO_DATE converts a string to the date type in the database

TO_DATE (char, 'format ')

TO_NUMBER converts a string to a number.

TO_NUMBER (char, 'format ')

Return to the system Oracle to obtain the system date, and output the data from 25 to 12 months to 09 months.

 
 

  
  
select sysdate from dual; 
 
 

Mi is minute, output 14:23:31

 
 

  
  
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual; 
 
 

Mm will display the month, output 14:12:31

 
 

  
  
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual; 
 
 

Output: 09-12-25 14:23:31

 
 

  
  
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual 
 
 

Output 14:23:31

 
 

  
  
select to_date('2009-12-25 14:23:31','yyyy-mm-dd,hh24:mi:ss') from dual 
 
 

If you write the above:

 
 

  
  
select to_date('2009-12-25 14:23:31','yyyy-mm-dd,hh:mi:ss') from dual 
 
 

An error is reported because the hour hh is in 12-digit format and 14 is invalid and cannot be matched.

Output $10,000, 00:

 
 

  
  
select to_char(1000000,'$99,999,99') from dual; 
 
 

Output RMB10, 000,00:

 
 

  
  
select to_char(1000000,'L99,999,99') from dual; 
 
 

Output 1000000.12:

 
 

  
  
select trunc(to_number('1000000.123'),2) from dual;  
  
  
select to_number('1000000.123') from dual;  
 
 

Conversion format:

Y indicates the last digit of year,

Yy indicates the last two digits of the year,

Yyy indicates the last three digits of the Year,

Yyyy indicates the year in four digits

Month: mm represents month,

Mon can be abbreviated as November, nov,

The full name of month, for example, January 1, November or November.

Dd indicates the day of the month,

Ddd indicates the day of the year,

The day of the week, short for dy, such as Friday or fri,

The day of the week, such as Friday or Friday.

Two-digit hh for hour indicates 12-digit hour,

Hh24 2-digit 24-hour

Indicates minute. mi 2-digit indicates minute.

Indicating second: two-digit ss indicates 60 seconds

Indicates the quarter. q indicates the quarter 1-4)

In addition, ww is used to indicate the week w of the current year to indicate the week w of the current month.

Time range in the 24-hour system: 00: 00: 00-23:59:59

Time range in the 12-hour format: 1: 00: 00-12:59:59

Number Format: 9 represents a number

0 force display 0

$ Place a $ character

L place a floating local currency character

. Display decimal point

, Display the thousands of indicators

Supplement:

Current Time minus 7 minutes

 
 

  
  
select sysdate,sysdate - interval '7' MINUTE from dual; 
 
 

Current Time minus 7 hours

 
 

  
  
select sysdate - interval '7' hour from dual; 
 
 

Current Time minus 7 days

 
 

  
  
select sysdate - interval '7' day from dual; 
 
 

Current Time minus July

 
 

  
  
select sysdate,sysdate - interval '7' month from dual; 
 
 

Current Time minus 7 years

 
 

  
  
select sysdate,sysdate - interval '7' year from dual; 
 
 

Time Interval multiplied by a number

 
 

  
  
select sysdate,sysdate - 8*interval '7' hour from dual; 
 
 

Description:

Dual pseudo Column

Dual is an existing table in Oracle that can be read by any user. It is often used in select statement blocks without a target table.

Different systems may return different formats for Oracle to obtain the system date.

Returns the user of the current connection: select user from dual