Oracle development topic: Date

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 ....Frequently used:Select * from payment tWhere transaction_date = to_date ('18-09-2008 ', 'dd-mm-yyyy ') Select * from payment t
Where last_update_date = to_date ('18-09-2008 11:59:14 ', 'dd-mm-yyyy hh24: MI: ss ')

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. Check 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;
Set the date language for Monday: Alter session set nls_date_language = 'American ';
It can also be like this: to_date ('2017-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 ('1970-02-28 ', 'yyyy-mm-dd')-to_date ('1970-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.0322580645161310. 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 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. How to handle 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. Differences 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.