Oracle to_date, to_char, to_number usage

Note: organize to Network

 

24
Hours
Hh24

Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual;

Select to_date ('1970-01-01 13:14:20 ', 'yyyy-mm-dd hh24: MI: ss') from dual;

 

To_date () function

1.
Date Format Parameters
Description

D
Day of the week

Day
The name of the day, filled with spaces
9
Characters

Dd
The day of the month

Ddd
Day of the year

Dy
Abbreviated name of day

IW ISO
Week of the standard year

Iyyy ISO
Standard four-digit year

Yyyy
Four-digit year

YYY, YY, y
The last three digits of the year, two digits, one digit

HH
Hour,
12
Hours

Hh24
Hour,
24
Hours

Mi
Minute

SS
Seconds

Mm
Month

Mon
Abbreviated month

Month
Full name of month

W
The week of the month.

WW
The week of the year
1.
Date interval operation

Current Time minus
7
Minutes

Select sysdate, sysdate-interval '7' minute from dual

Current Time minus
7
Hour time

Select sysdate-interval '7' hour from dual

Current Time minus
7
Day

Select sysdate-interval '7' day from dual

Current Time minus
7
Month time

Select sysdate, sysdate-interval '7' month from dual

Current Time minus
7
Year

Select sysdate, sysdate-interval '7' year from dual

Time Interval multiplied by a number

Select sysdate, sysdate-8 * interval '2' hour from dual

 

2.
Date to character operations

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: MI: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-DDD hh: MI: ss') from dual

Select sysdate, to_char (sysdate, 'yyyy-mm IW-D hh: MI: ss') from dual

Reference
Oracle
Related Documentation
(Oracle901doc/server.901/a90125/SQL _elements4.htm #48515)

 

3.
Character-to-date operations

Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: MI: ss') from dual

Usage and
To_char
Similar.

4. to_number
Use the to_number function to convert characters to numbers.
To_number (char [, 'format'])

Digit 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

 

 

Description of the to_date parameter in Oracle

 

1. Description of date format parameters

D. day of the week
The name of day, which is filled with spaces to 9 characters.
Day of DD month
The day of the year in DDD
Short Name of Dy day
Week of the Year of the iw iso Standard
Four-digit year of the iyyy ISO Standard
Yyyy four-digit year
Last three digits of YYY, YY, and Y years, two digits, one digit
HH hours, at 12 hours
Hh24 hours, in 24 hours
Mi score
SS seconds
Mm Month
Abbreviated month of Mon
Full name of month
W the week of the month
The day of the week in WW 1. Date interval operation
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 '2' hour from dual
2. Date-to-character operations

Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: MI: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-DDD hh: MI: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm IW-D hh: MI: ss') from dual
Refer to the Oracle documentation (oracle901doc/server.901/a90125/SQL _elements4.htm #48515)
3. Character-to-date operations

Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: MI: ss') from dual
The usage is similar to that of to_char.
4. Use of the trunk/Round Function

Select trunc (sysdate, 'Year') from dual
Select trunc (sysdate) from dual
Select to_char (trunc (sysdate, 'yyyy'), 'yyyy') from dual
5. Oracle has millisecond-level data types

-- Returns the current time year month hour minute second millisecond
Select to_char (current_timestamp (5), 'dd-MON-YYYY hh24: MI: ssxff') from dual;
-- Returns the second millisecond of the current time. The precision after the second can be specified (max = 9)
Select to_char (current_timestamp (9), 'mi: ssxff ') from dual;
6. computing program running time (MS)

Declare
Type RC is ref cursor;
Rochelle RC;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Begin
For I in 1000
Loop
Open l_rc
'Select object_name from all_objects '|
'Where object_id = '| I;
Fetch l_rc into l_dummy;
Close l_rc;
End loop;
Dbms_output.put_line
(Round (dbms_utility.get_time-l_start)/100, 2) |
'Seconds
...');

End;