Date-related functions

Source time: to_char (datevalue ));
Source time minus 1 day: to_char (DateValue-1 ));
Source time minus 1 day 1 hour: to_char (DateValue-1-1/24 ));
Source time minus 1 day 1 hour 1 minute: to_char (DateValue-1-1/24-1/(24*60 )));
Source time minus 1 day 1 hour 1 minute 1 second: to_char (DateValue-1-1/24-1/(24*60)-1/(24*60*60 )));

Source time minus January add_months (TM,-1)

Oracle processing date overview

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. Days of the 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
Select sysdate, to_char (sysdate, 'hh') from dual;
Select sysdate, to_char (sysdate, 'hh24') from dual;

Obtain the year, month, and day.
13. Processing of the 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. Methods for changing the number of days in a month
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

15. 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.

16. Differences between yyyy and rrrr
'Yyyy99 TO_C
-----------
Yyyy 99 0099
Rrrr 99 1999
Yyyy 01 0001
Rrrr 01 2001

17. Processing of different time zones
Select to_char (NEW_TIME (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: mi: ss'), sysdate
From dual;

18. 5 seconds at an interval
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

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

20. Calculation 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 (date2-date1, 365), 30) as daily.
21. 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

Date functions in oracle

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.
Select add_months (sysdate, 12) "Next Year" from dual;
Select add_months (sysdate, 112) "Last Year" from dual;

2. current_date () returns the current date in the current time zone.
Select sessiontimezone, current_date from dual;
Select sessiontimezone, current_timestamp from dual;

3. current_timestamp () returns the current date in the current time zone as the timestamp with time zone Data Type
Select sessiontimezone, current_timestamp from dual;
Select sessiontimezone, current_timestamp from dual;

4. dbtimezone () returns the time zone
Select dbtimezone from dual;

5. extract () identifies the field value of the date or interval value
Select extract (month from sysdate) "This Month" from dual;
Select extract (year from add_months (sysdate, 36) "3 Years Out" from dual;

6. last_day () returns the date of the last day of the month that contains the date parameter.
Select last_day (date '2017-02-01 ') "Leap Yr? "From dual;
Select last_day (sysdate) "Last day of this month" from dual;

7. localtimestamp () returns the date and time in the session.
Select localtimestamp from dual;

Select localtimestamp, current_timestamp from dual;

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

8. months_between () determines the number of months between two dates.
Select months_between (sysdate, date '2017-05-18 ') from dual;
Select months_between (sysdate, date '2017-01-01 ') from dual;

 

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)

Week-related date correspondence

1. 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. 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. 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'

4. Calculate the area average (group average, two time differences: small time difference, minute difference)

-- Query of rainfall in all conforming Conditions
Select zc_quyu, sum (DRP)/round (count (distinct (B. stcd), 3) sumrn
From a A, B
Where. stcd = B. stcd and to_char (TM, 'yyyy-mm-dd hh24: MI: ss') <= '2017-08-17 16:00:00 'and to_char (TM, 'yyyy-mm-dd hh24: MI: ss')> = '2017-08-17 0:00:00 'and to_char (TM, 'Miss') = '2016' and. stcd not in (
Select stcd from a where to_char (tm, 'yyyy-mm-dd hh24: mi: ss') <= '2017-08-17 16:00:00 'and to_char (tm, 'yyyy-mm-dd hh24: mi: ss')> '2017-08-17 0:00:00 'and to_char (tm, 'Miss') = '000000' group by stcd
Having count (stcd )! = (To_date ('2017-08-17 0:00:00 ', 'yyyy-mm-dd hh24: 00: 00')-to_date ('2017-08-17 16:00:00 ', 'yyyy-mm-dd hh24: 00: 00') * 24
) Group by quyu

Time Difference

Select to_number (to_date ('2017-06-20 20:34 ', 'yyyy-mm-dd hh24: mi')-to_date ('2017-06-19 ', 'yyyy-mm-dd
Hh24: mi ') * 24 from dual;

Default time period and date difference
Select to_date ('2017-06-20 ', 'yyyy-mm-dd hh24: mi')-to_date ('2017-06-19 ', 'yyyy-mm-dd
Hh24: mi ') from dual;

 

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/yfly13/archive/2010/02/08/5299103.aspx