Summary of Date and Time Functions in Oracle

Processing date in Oracle

To_date format
Day:
Dd number 12
Dy abbreviated Fri
Day spelled out Friday
Ddspth spelled out, ordinal twelfth
Month:
MM number 03
Mon abbreviated MAR
Month spelled out March
Year:
YY two digits 98
Yyyy four digits 1998

The time range in the 24-hour format is: 0:00:00-23:59:59 ....
The time range in the 12-hour format is: 1:00:00-12:59:59 ....
1.
Date and character conversion function usage (to_date, to_char)

2.
Select to_char (to_date (222, 'J'), 'jsp ') from dual

Display two hundred twenty-two

3.
The day of the week.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day') from dual;
Monday
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ date_language = American') from dual;
Monday
Set the date language
Alter session set nls_date_language = 'American ';
You can also
To_date ('1970-08-26 ', 'yyyy-mm-dd', 'nls _ date_language = American ')

4.
Number of days in a two-day period
Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;

5. Use a time of null
Select ID, active_date from Table1
Union
Select 1, to_date (null) from dual;

Note that to_date (null) is used)

6.
A_date between to_date ('20140901', 'yyyymmdd') and to_date ('20140901', 'yyyymmdd ')
Therefore, it is not included in this range after on January 1, December 31 and before on January 1, December 1.
Therefore, when the time needs to be accurate, to_char is still necessary.
7. Date Format conflict
The input format depends on the type of the Oracle character set you installed, for example, us7ascii. The date format is '01-Jan-01'
Alter system set nls_date_language = American
Alter session set nls_date_language = American
Or write it in to_date.
Select to_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ date_language = American') from dual;
Note that I just mentioned nls_date_language. Of course there are many more,
Available
Select * From nls_session_parameters
Select * from V $ nls_parameters

8.
Select count (*)
From (select rownum-1 (rnum)
From all_objects
Where rownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd')-to_date ('2017-
02-01 ', 'yyyy-mm-dd') + 1
)
Where to_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + rnum-1, 'd ')
Not
In ('1', '7 ')

Find the number of days between and except Monday and seven
Call dbms_utility.get_time to subtract the result (1/100 seconds instead of milliseconds ).

9.
Select months_between (to_date ('01-31-1999 ', 'Mm-DD-YYYY '),
To_date ('12-31-1998 ', 'Mm-DD-YYYY') "months" from dual;
1

Select months_between (to_date ('02-01-1999 ', 'Mm-DD-YYYY '),
To_date ('12-31-1998 ', 'Mm-DD-YYYY') "months" from dual;

1.03225806451613
10. next_day usage
Next_day (date, Day)

Monday-Sunday, for format code day
Mon-sun, for format code DY
1-7, for format code D

11
Select to_char (sysdate, 'hh: MI: ss') time from all_objects
Note: the time of the first record is the same as that of the last record.
You can create a function to solve this problem.
Create or replace function sys_date return date is
Begin
Return sysdate;
End;

Select to_char (sys_date, 'hh: MI: ss') from all_objects;
12.
Hours

Select extract (hour from timestamp '2017-02-16 2:38:40 ') from offer
SQL> select sysdate, to_char (sysdate, 'hh') from dual;

Sysdate to_char (sysdate, 'hh ')
-----------------------------------------
2003-10-13 19:35:21 07

SQL> select sysdate, to_char (sysdate, 'hh24') from dual;

Sysdate to_char (sysdate, 'hh24 ')
-------------------------------------------
19:35:21 19

Obtain the year, month, and day.
13.
Processing of year, month, and day
Select older_date,
Newer_date,
Years,
Months,
ABS (
Trunc (
Newer_date-
Add_months (older_date, years * 12 + months)
)
) Days
From (select
Trunc (months_between (newer_date, older_date)/12) years,
MoD (trunc (months_between (newer_date, older_date )),
12) months,
Newer_date,
Older_date
From (select hiredate older_date,
Add_months (hiredate, rownum) + rownum newer_date
From EMP)
)

14.
How to deal with the uncertainty of the number of days in a month
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

16.
Find the number of days this year
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual

How to deal with a leap year
To_char (last_day (to_date ('02 '|: Year, 'mmyyyy'), 'dd ')
If it is 28, it is not a leap year.

17.
Difference between yyyy and RRRR
'Yyyy99 to_c
-----------
Yyyy 99 0099
Rrrr 99 1999
Yyyy 01 0001
Rrrr 01 2001

18. Processing in different time zones
Select to_char (new_time (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: MI: ss'), sysdate
From dual;

19.
One interval in five seconds
Select to_date (floor (to_char (sysdate, 'ssss')/300) * 300, 'ssss'), to_char (sysdate, 'ssss ')
From dual

2002-11-1 9:55:00 35786
Sssss indicates five seconds

20.
The day of the year
Select to_char (sysdate, 'ddd '), sysdate from dual
310 10:03:51

21. Computing hour, minute, second, millisecond
Select
Days,
A,
Trunc (A * 24) hours,
Trunc (A * 24*60-60 * trunc (A * 24) minutes,
Trunc (A * 24*60*60-60 * trunc (A * 24*60) seconds,
Trunc (A * 24*60*60*100-100 * trunc (A * 24*60*60) mseconds
From
(
Select
Trunc (sysdate) days,
Sysdate-trunc (sysdate)
From dual
)

Select * From tabname
Order by deCODE (mode, 'fifo ', 1,-1) * to_char (RQ, 'yyyymmddhh24miss ');

//
Floor (date2-date1)/365) as year
Floor (date2-date1, 365)/30) as month
MoD (mod (date2-date1, 365), 30) as daily.
23. next_day Function
Next_day (sysdate, 6) is the next Friday from the current start. The following number is counted from Sunday.
1 2 3 4 5 6 7
October 6, 1234

 

Oracle has many date functions.

There are many date functions in Oracle, such:
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)
Example:
SQL> select add_months (sysdate, 12) "next year" from dual;

Next year
----------
April 11-04

SQL> select add_months (sysdate, 112) "last year" from dual;

Last year
----------
13-3-13

SQL>

2. current_date () returns the current date in the current time zone.
Date_value: = current_date
SQL> column sessiontimezone for A15
SQL> select sessiontimezone, current_date from dual;

Sessiontimezone current_da
-------------------------
+-11-03

SQL> alter session set time_zone = '-11: 00'
2/

The session has been changed.

SQL> select sessiontimezone, current_timestamp from dual;

Sessiontimezone current_timestamp
---------------------------------------------------
--03 04.59.13.668000 pm-11:
00

SQL>

3. current_timestamp () returns the current date in the current time zone as the timestamp with Time 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
---------------------------------------------------
+-03 11.56.28.160000 AM + 08:
00

SQL> alter session set time_zone = '-11: 00'
2/

The session has been changed.

SQL> select sessiontimezone, current_timestamp from dual;

Sessiontimezone current_timestamp
---------------------------------------------------
--03 04.58.00.243000 pm-11:
00

SQL>

4. dbtimezone () returns the time zone
Varchar_value: = dbtimezone
SQL> select dbtimezone from dual;

Dbtime
------
-07:00

SQL>

5. Extract () identifies the field value of the date or interval 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 '2017-02-01 ') "Leap yr? "From dual;

Leap yr?
----------
29-2-00

SQL> select last_day (sysdate) "last day of this month" from dual;

Last day o
----------
30-11-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
----------------------------
13-11-03 12.09.15.433000
Afternoon

SQL> select localtimestamp, current_timestamp from dual;

Localtimestamp current_timestamp
----------------------------------------------------------------
13-11-03 12.09.31.006000 13-11-03 12.09.31.006000 PM + 08:
00 pm

SQL> alter session set time_zone = '-11: 00 ';

The session has been changed.

SQL> select localtimestamp, to_char (sysdate, 'dd-MM-YYYY hh: MI: SS am') "sysdate" from dual;

Localtimestamp sysdate
----------------------------------------------------
12-11-03 05.11.31.259000 13-11-2003 12:11:31 pm
Afternoon

SQL>

8. months_between () determines the number of months between two dates.
Number_value: = months_between (date_value, date_value)
SQL> select months_between (sysdate, date '2017-05-18 ') from dual;

Months_between (sysdate, date '2017-05-18 ')
----------------------------------------
389.855143

SQL> select months_between (sysdate, date '2017-01-01 ') from dual;

Months_between (sysdate, date '2017-01-01 ')
----------------------------------------
34.4035409

SQL>

9. Given a date value, next_day () returns the date value that appears for the first time on the day indicated by the second parameter (the name string of the corresponding day should be returned)

 

Weekly date function

1. query the first day of a week
Select trunc (decode (WW, 53, to_date (yy | '000000', 'yyyyddmm'), to_date (yy | '-' | to_char (WW * 7 ), 'yyyy-DDD '), 'D') last_day
From (select substr ('1970-32', 1, 4) YY, to_number (substr ('1970-32', 6) ww
From dual)

Select trunc (to_date (substr ('1970-01', 2003) | to_char (to_number (substr ('1970-01', 6) * 7 ), 'yyyy-DDD '), 'D')-6 first_day from dual

Select min (v_date) from
(Select (to_date ('20170101', 'yyyymmm ') + rownum) v_date
From all_tables
Where rownum <370)
Where to_char (v_date, 'yyyy-iw') = '2017-49'

2. query the last day of a week
Select trunc (decode (WW, 53, to_date (yy | '000000', 'yyyyddmm'), to_date (yy | '-' | to_char (WW * 7 ), 'yyyy-DDD '), 'D')-6 first_day
From (select substr ('2014-33 ', 1, 4) YY, to_number (substr ('2014-33', 6) ww
From dual)

Select trunc (to_date (substr ('1970-01', 2003) | to_char (to_number (substr ('1970-01', 6) * 7 ), 'yyyy-DDD '), 'D') last_day from dual

Select max (v_date) from
(Select (to_date ('20170101', 'yyyymmm ') + rownum) v_date
From all_tables
Where rownum <370)
Where to_char (v_date, 'yyyy-iw') = '2017-33'

3. query the date of a week
Select min_date, to_char (min_date, 'day') day from
(Select to_date (substr ('1970-33', 2004) | '001' + rownum-1, 'yyyyddd ') min_date
From all_tables
Where rownum <= decode (mod (to_number (substr ('1970-33', 2004), 4), 0,366,365)
Union

Select to_date (substr ('2014-33 ', 2004)-1 |
Decode (mod (to_number (substr ('1970-33', 2004)-0,359,358),) + rownum, 'yyyyddd ') min_date
From all_tables
Where rownum <= 7
Union

Select to_date (substr ('1970-33', 2004) + 1 | '001' + rownum-1, 'yyyyddd ') min_date
From all_tables
Where rownum <= 7
)
Where to_char (min_date, 'yyyy-iw') = '2017-33'

 

Time operations in Oracle

In the forum, I often see questions about Oracle time operations. Today I have time to read the posts of my old siblings, sort them out, and give an example, I hope it will help you.
First of all, I would like to thank Ern, Eric. Li and the moderators for their enthusiasm.

The content is as follows:
1. Oracle supports date calculation
2. Date calculation is performed in days
3. If you need to calculate the value in smaller units such as minute and second, convert the value by time.
4. Pay attention to brackets when performing time-based conversion (see red brackets in the example). Otherwise, problems may occur.

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh: MI: ss ';

The session has been changed.

SQL> set serverout on
SQL> declare
2 datevalue date;
3 begin
4 select sysdate into datevalue from dual;
5 dbms_output.put_line ('source time: '| to_char (datevalue ));
6 dbms_output.put_line ('source time minus 1 day: '| to_char (DateValue-1 ));
7 dbms_output.put_line ('source time minus 1 day 1 hour: '| to_char (DateValue-1-1/24 ));
8 dbms_output.put_line ('source time minus 1 day 1 hour 1 minute: '| to_char (DateValue-1-1/24-1/(24*60 )));
9 dbms_output.put_line ('source time minus 1 day 1 hour 1 minute 1 second: '| to_char (DateValue-1-1/24-1/(24*60)-1/(24*60*6
0 )));
10 end;
11/
Source time: 2003-12-29 11:53:41
Source time minus 1 day: 11:53:41
Source time minus 1 day 1 hour: 2003-12-28 10:53:41
Source time minus 1 day 1 hour 1 minute: 10:52:41
Source time minus 1 day 1 hour 1 minute 1 second: 10:52:40

The PL/SQL process is successfully completed.