Oracle About time/date operations _oracle

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