ORACLE single-row function and multi-row function 4: Date function example

Lab environment: BYS @ bys1select * fromnls_session_parameterswhereparameterNLS_DATE_FORMAT; PARAMETERVALUE--------------------------------------------------NLS_DATE _ FORMATyyyymmddhh24: mi: ssBYS @ bys1showparameternls_langNAMETYPE

Tutorial environment: BYS @ bys1select * from nls_session_parameters where parameter = 'nls _ DATE_FORMAT '; parameter value contains parameter NLS_DATE_FORMAT yyyy/mm/dd hh24: mi: ss BYS @ bys1show parameter nls_lang NAME TYPE

Lab Environment:

BYS @ bys1> select * from nls_session_parameters where parameter = 'nls _ DATE_FORMAT ';
PARAMETER VALUE
--------------------------------------------------
NLS_DATE_FORMAT yyyy/mm/dd hh24: mi: ss

BYS @ bys1> show parameter nls_lang
NAME TYPE VALUE
-----------------------------------------------------------------------------

Nls_language string AMERICAN

1. directly use SYSDATE plus or minus numbers to operate the date

The date + or-1 represents the plus or minus one day. If it is an hour or a few minutes, you can use the day/hour method.

As shown in the following statement, 1 hour is 1/24; 5 minutes is 1/24/12. 86400: 1 day = 24 hours = 24*60*60 = 86400 seconds

BYS @ bys1> select sysdate + 365, sysdate-1, sysdate-3/24, sysdate-1/24/12 from dual;
SYSDATE + 365 SYSDATE-1 SYSDATE-3 SYSDATE-1/24 SYSDATE-1/24/12
-----------------------------------------------------------------------------------------------

19:26:15 19:26:15 10/30/02:26:152013/2013/11 2013/11/02:26:15/02:

2. The TIMESTAMP records the year, month, day, hour, minute, second, and second.

SYSTIMESTAMP returns timestamp with time zone data. + Indicates that the current region is GMT +.
BYS @ bys1> select policimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
02-NOV-13 09.08.04.390741 PM + 08:00
The display format of timestamp is different from that of SYSDATE.
BYS @ bys1> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24: mi: ss. ff ';
Session altered.

BYS @ bys1> select policimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
02-NOV-13 09.11.19.258161 PM + 08:00

The TIMESTAMP method:
-To_timestamp ('2017-02-09 23:59:59. 000', 'yyyy-mm-dd hh24: mi: ss. ff ')
-Timestamp '2017-04-05 13:48:00. 123'
-The separators in to_timestamp can be changed. The date Separator in timestamp must be-, the time must be:, and the second must be followed.
-The timestamp can be precise to milliseconds, microseconds, or even nanoseconds.
Default value when no value is specified during conversion: Year in SYSDATE; month: Month in SYSDATE; Day: 1; hour, minute, and second: both 0
BYS @ bys1> col a3 for a30
BYS @ bys1> col a2 for a30
BYS @ bys1> col a1 for a30
BYS @ bys1> select to_timestamp ('05 13', 'yy hh24') as a1, to_timestamp ('05 13', 'Mm Mi') as a2, to_timestamp ('05 13', 'dd ss') as a3 from dual;
A1 A2 A3
------------------------------------------------------------------------------------------
13:00:00. 000000000 00:13:00. 000000000
Description: The timestamp indicated by FF5 can be a microsecond of no more than 5 characters. If the timestamp has three characters in microseconds and is converted to FF2, an error is returned.

At the same time, a maximum of 9 digits can be specified in seconds.
BYS @ bys1> select to_timestamp ('05 13:48:22. 100', 'dd HH24: MI: SS. ff5') from dual;
TO_TIMESTAMP ('2014: 48: 808080', 'ddhh24: MI: SS. ff5 ')
---------------------------------------------------------------------------
13:48:22. 778000000

Note:

BYS @ bys1> select to_timestamp ('05 13:48:22. 100', 'dd HH24: MI: SS. ff2') from dual;
Select to_timestamp ('05 13:48:22. 778 ', 'dd HH24: MI: SS. ff2') from dual
*
ERROR at line 1:
ORA-01880: the fractional seconds must be between 0 and 999999999

BYS @ bys1> select to_timestamp ('05 13:48:22. 100', 'dd HH24: MI: SS. ff9') from dual;
TO_TIMESTAMP ('2014: 48: 808080', 'ddhh24: MI: SS. ff9 ')
---------------------------------------------------------------------------
13:48:22. 123456789
BYS @ bys1> select to_timestamp ('05 13:48:22. 100', 'dd HH24: MI: SS. ff9') from dual;
Select to_timestamp ('05 13:48:22. 1234567890 ', 'dd HH24: MI: SS. ff9') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
BYS @ bys1> select to_timestamp ('05 13:48:22. 100', 'dd HH24: MI: SS. ff10') from dual;
Select to_timestamp ('05 13:48:22. 1234567890 ', 'dd HH24: MI: SS. ff10') from dual
*
ERROR at line 1:
ORA-01821: date format not recognized

3. The date function can only represent a date, not a time. The following is an example of an application in 4.

Default Value: Year in SYSDATE; month: Month in SYSDATE; Day: 1; hour, minute, and second: both are 0

4. Determine whether the specified date is a certain day. In to_date and date, if only the specified date is not specified, the default value is 00:00:00. That is, the next second of the day before 23:59:59.

Note that between and is equal to greater than or equal to AND less than or equal. Therefore, it should belong to a certain day, from 0 s at 23:59:59 of the current day to S of the current day. 1 day divided by 86400 is 1 second

BYS @ bys1> select 'true' from dual where to_date ('2017-11-02 21:48:22 ', 'yyyy-MM-DD HH24: MI: ss ') between date '2017-11-01 'and date '2017-11-06'-2013;

'Tru
----
TRUE

BYS @ bys1> select 'true' from dual where to_date ('2017/02 21:45:43 ', 'yyyy-MM-DD HH24: MI: ss') between to_date ('2017-11-02 ', 'yyyy-mm-dd') and to_date ('1970-11-03 ', 'yyyy-mm-dd hh24: mi: ss')-2013;

'Tru
----
TRUE
You can also use to_date to explicitly convert the date.
Select 'true' from dual where to_date ('2017/02 21:45:43 ', 'yyyy-MM-DD HH24: MI: ss') between to_date ('2017-11-02 ', 'yyyy-mm-dd') and to_date ('1970-11-03 ', 'yyyy-mm-dd hh24: mi: ss')-2013;

'True'
------
TRUE

Note that between and is equal to or greater than or equal

BYS @ bys1> select 'true' from dual where to_date ('2017/02 21:45:43 ', 'yyyy-MM-DD HH24: MI: ss')> = date '2017-11-02 'and to_date ('2017/02 21:45:43', 'yyyy-MM-DD HH24: MI: ss') <= date' 2013-'-2013/11;
'Tru
----
TRUE
In fact, it can also be expressed as less than or equal to November 3 23:59:59 seconds.

BYS @ bys1> select 'true' from dual where to_date ('2017/02 23:59:59 ', 'yyyy-MM-DD HH24: MI: ss')> = date '2017-11-02 'and to_date ('2017/02 23:59:59', 'yyyy-MM-DD HH24: MI: ss') <date '2017 ';
'Tru
----
TRUE

5. MONTHS_BETWEEN (a, B): indicates the difference between the months of a and B. It is a-B. If a is later than B, that is, it is a positive number; otherwise, it is a negative number. BYS @ bys1> Select EMPNO, HIREDATE, MONTHS_BETWEEN (Sysdate, HIREDATE)/12 dday, sysdate From EMP where rownum <3;
EMPNO HIREDATE DDAY SYSDATE
----------------------------------------------------------
7369 00:00:00 32.8784294 18:37:05
7499 00:00:00 32.7036983 18:37:05
BYS @ bys1> select months_between (sysdate, to_date ('2017/11 11:11:11 ', 'yyyy-mm-dd hh24: mi: ss') as dday from dual;
DDAY
----------
23.7196307
BYS @ bys1> select months_between (to_date ('2017/11 11:11:11 ', 'yyyy-mm-dd hh24: mi: ss'), sysdate) as dday from dual;
DDAY
----------
-23.719639

6. ADD_MONTHS: Add a month to the specified date, that is, the date after N months. If the current date plus the specified number of months exceeds one year, the year is automatically increased. BYS @ bys1> select add_months (sysdate, 1), add_months (sysdate, 4) from dual;
ADD_MONTHS (SYSDATE, ADD_MONTHS (SYSDATE,
--------------------------------------

18:39:23 18:39:23

7. NEXT_DAY: indicates the date BYS @ bys1> select next_day (sysdate, 'sunday'), next_day (sysdate, 'tuesday') based on the current time ') from dual;
NEXT_DAY (SYSDATE,'s NEXT_DAY (SYSDATE,'t
--------------------------------------

19:34:20 19:34:20

8. LAST_DAY: Calculate the last day of the current date, that is, the last day of the current month. BYS @ bys1> select last_day (sysdate) from dual;
LAST_DAY (SYSDATE)
-------------------

18:43:16

9. Use ROUND: The date can only be rounded to the year, month, day, hour, and minute. The second cannot be operated.
BYS @ bys1> select round (sysdate, 'yy') as year, round (sysdate, 'mm') as month, round (sysdate, 'dd') as day, round (sysdate, 'hh') as hour, round (sysdate, 'hh24') as hour24, round (sysdate, 'mi') as minutes from dual;
Year month day hour HOUR24 MINUTES
------------------------------------------------------------------------------------------------------------------
00:00:00 00:00:00 00:00:00 19:00:00
BYS @ bys1> select round (sysdate, 'ss') as sss from dual;
Select round (sysdate, 'ss') as sss from dual
*
ERROR at line 1:

ORA-01899: bad precision specifier

10. TRUNC: TRUNC is used to intercept the date. BYS @ bys1> set linesize 200
BYS @ bys1> select trunc (sysdate, 'yy') as year, trunc (sysdate, 'mm') as month, trunc (sysdate, 'dd') as day, trunc (sysdate, 'hh') as hour, trunc (sysdate, 'hh24') as hour24, trunc (sysdate, 'mi') as minutes from dual;
Year month day hour HOUR24 MINUTES
------------------------------------------------------------------------------------------------------------------
00:00:00 00:00:00 00:00:00 18:00:00
You can only intercept the year, month, day, hour, and minute. You cannot intercept the second.
BYS @ bys1> select trunc (sysdate, 'ss') as sss from dual;
Select trunc (sysdate, 'ss') as sss from dual
*
ERROR at line 1:
ORA-01899: bad precision specifier