There are many functions about dates in Oracle, such as:
1,add_months () is used to increase or decrease the number of months from a date value
Date_value:=add_months (date_value,number_of_months)
Cases:
Sql> Select Add_months (sysdate,12) "Next year" from dual;
Next year
----------
1 March-November-04
Sql> Select Add_months (sysdate,112) "Last year" from dual;
Last year
----------
1 March-March-13
Sql>
2.current_date () returns the current date that will be placed in the time zone
Date_value:=current_date
sql> column Sessiontimezone for A15
Sql> select Sessiontimezone,current_date from dual;
Sessiontimezone Current_da
--------------- ----------
+08:00 1 March-November-03
Sql> alter session set Time_zone= ' -11:00 '
2/
The session has changed.
Sql> select Sessiontimezone,current_timestamp from dual;
Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.59.13.668000 Afternoon-11:
00
Sql>
3,current_timestamp () returns the current date in the time zone by timestamp with the zone data type
Timestamp_with_time_zone_value:=current_timestamp ([timestamp_precision])
sql> column Sessiontimezone for A15
sql> Column Current_timestamp format A36
Sql> select Sessiontimezone,current_timestamp from dual;
Sessiontimezone Current_timestamp
--------------- ------------------------------------
+08:00 1 March-November-03 11.56.28.160000 a.m. + 08:
00
Sql> alter session set Time_zone= ' -11:00 '
2/
The session has changed.
Sql> select Sessiontimezone,current_timestamp from dual;
Sessiontimezone Current_timestamp
--------------- ------------------------------------
-11:00 December-November-03 04.58.00.243000 Afternoon-11:
00
Sql>
4,Dbtimezone () return time zone
Varchar_value:=dbtimezone
Sql> select Dbtimezone from dual;
DBTIME
------
-07:00
Sql>
5,Extract () to find the date or interval value of the field value
Date_value:=extract (Date_field from [Datetime_value|interval_value])
Sql> Select Extract (month from sysdate) ' this month ' from dual;
This Month
----------
11
Sql> Select Extract (Year from add_months (sysdate,36)) "3 Years out" from dual;
3 Years out
-----------
2006
Sql>
6,last_day () returns the date of the last day of the month that contains the date parameter
Date_value:=last_day (Date_value)
Sql> Select Last_day (Date ' 2000-02-01 ') "Leap Yr?" from dual;
Leap Yr?
----------
2 September-February-00
Sql> Select Last_day (sysdate) "Last day of this month" from dual;
Last Day O
----------
30月-November-03
Sql>
7,Localtimestamp () returns the date and time in the session
Timestamp_value:=localtimestamp
sql> Column Localtimestamp Format A28
Sql> select Localtimestamp from dual;
Localtimestamp
----------------------------
1 March-November-03 12.09.15.433000
Afternoon
Sql> select Localtimestamp,current_timestamp from dual;
Localtimestamp Current_timestamp
---------------------------- ------------------------------------
1 March-November-03 12.09.31.006000 1 March-November-03 12.09.31.006000 pm + 08:
Afternoon 00
Sql> alter session set time_zone= ' -11:00 ';
The session has changed.
Sql> Select Localtimestamp,to_char (sysdate, ' dd-mm-yyyy HH:MI:SS AM ') "Sysdate" from dual;
Localtimestamp sysdate
---------------------------- ------------------------
December-November-03 05.11.31.259000 13-11-2003 12:11:31 pm
Afternoon
Sql>
8,Months_between () to determine the number of months between two dates
Number_value:=months_between (Date_value,date_value)
Sql> Select Months_between (sysdate,date ' 1971-05-18 ') from dual;
Months_between (sysdate,date ' 1971-05-18 ')
----------------------------------------
389.855143
Sql> Select Months_between (sysdate,date ' 2001-01-01 ') from dual;
Months_between (sysdate,date ' 2001-01-01 ')
----------------------------------------
34.4035409
Sql>
9,Next_day () given a date value, returns the date value indicated by the second argument for the first time out now (the name string of the corresponding day should be returned)
Description
Single-line Date function
Single-line date functions manipulate data data types, most of which have data type parameters, and the vast majority of returns are values of data types.
10,add_months (,)
Returns the result of date D plus I months. I can make any integer. If I is a decimal, then the database converts the implicit form to an integer, which will intercept the part following the decimal point.
11,Last_day ()
function returns the last day of the month that contains the date D
12,Months_between (,)
Returns the number of months between D1 and D2, if the date of the D1 and D2 is the same, or if both are the last day of the month, an integer is returned, otherwise the result returned will contain a fraction.
13,New_time (,,)
D1 is a date data type that returns the date and time in the time zone tz2 when the date and time in the TZ1 is D. TZ1 and TZ2 strings.
14,Next_day (,)
Returns the first day of a condition given by Dow after date D, Dow uses the language given in the current session to specify a day of the week that returns the same time component as D.
Select Next_day (' 01-jan-2000 ', ' Monday ') "1st Monday", Next_day (' 01-nov-2004 ', ' Tuesday ') +7 "2nd Tuesday") from dual;1st Monday 2nd tuesday03-jan-2000 09-nov-2004
15,round ([,])
Rounds the date d in the format specified by FMT, FMT as a string.
16,Syadate
The function has no arguments and returns the current date and time.
17,trunc ([,])
Returns the date d of the unit specified by FMT.
Single-line conversion functions
Single-line conversion functions are used to manipulate multiple data types and to convert between data types.
18,Chartorwid ()
C makes a string that converts C to the Rwid data type.
Select test_id from Test_case where Rowid=chartorwid (' aaaa0saacaaaaliaaa ')
19,convert (, [,])
C-tailed strings, Dset, Sset are two character sets, the function converts string C from the Sset character set to the Dset character set, and the Sset defaults to the database's character set.
20,Hextoraw ()
X is a 16-binary string that converts the 16-in-X to raw data type.
21,Rawtohex ()
X is the raw data type string, which converts the raw data class to a 16-binary data type.
22,Rowidtochar ()
function converts a ROWID data type to a char data type.
23,To_char ([[,)
X is a data or number datatype that converts x to the char data type in the FMT specified format, if x is the date Nlsparm=nls_date_language control the language in which the month and day of the return are used. If x is the numeric nlsparm=nls_numeric_characters used to specify the decimal and thousand separator characters, and the currency symbol.
nls_numeric_characters = "DG", nls_currency= "string"
24,to_date ([, [,)
C represents a string, FMT represents a special form of string. Returns the language used by the C,nlsparm represented in FMT format. function converts the string C to a date data type.
25,To_multi_byte ()
C represents a string that converts a burden-truncated character of C into multibyte characters.
26,To_number ([, [,)
C represents a string, FMT represents a special format string, and the function return value is displayed in the format specified by FMT. Nlsparm represents the language, and the function returns the number represented by C.
27,To_single_byte ()
Converts a multibyte character in string C to an equivalent single-byte character. This function is used only if the database character set contains both Single-byte and multibyte characters.
1. Date-time interval operation
Current time minus 7 minutes of time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual
The current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual
The current time minus 7 days
Select Sysdate-interval ' 7 ' from dual
Current time minus July time
Select Sysdate,sysdate-interval ' 7 ' month from dual
The current time minus 7 years time
Select Sysdate,sysdate-interval ' 7 ' from dual
Time interval multiplied by a number
Select Sysdate,sysdate-8 *interval ' 2 ' hour from dual
2. Date to character operation
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 Oracle's related documentation (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4. htm#48515)
3. Character to date operation
Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual
The specific usage is similar to the to_char above.
4. Use of 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 data type with millisecond level
--Returns the current time, day, hour, and millisecond.
Select To_char (Current_timestamp (5), ' dd-mon-yyyy HH24:MI:SSxFF ') from dual;
--Returns the second millisecond of the current time, specifying the precision after the second (Max =9)
Select To_char (Current_timestamp (9), ' Mi:ssxff ') from dual;
6. Calculation program Running time (MS)
Declare
Type RC is REF CURSOR;
L_RC RC;
L_dummy All_objects.object_name%type;
L_start number default dbms_utility.get_time;
Begin
For I in 1. 1000
Loop
Open L_RC for
' 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