To_date format in Oracle

To_date format (take time: 13:45:25 as an example)
Year:
YY two digits two-year display value: 07
YYY three digits: 007
Yyyy four digits four-digit year display value: 2007
Month:
Mm Number: 11
Mon abbreviated Character Set indicates the displayed value: January 1, November. If the English version is used, Nov is displayed.
Month spelled out Character Set indicates the displayed value: January 1, November. If the English version is used, November is displayed.
Day:
Dd number: 02
Ddd number: 02
Short display value of Dy abbreviated for the day of the week: Friday. If it is an English version, Fri is displayed.
Day spelled out full display value for the day of the week: Friday. If the English version is used, the display is Friday.
Ddspth spelled out, ordinal twelfth
Hour:
HH two digits 12 hour display value: 01
Hh24 two digits 24 hours: 13
Minute:
Mi Two digits 60 hexadecimal value: 45
Second:
SS two digits 60 hexadecimal value: 25
Others
Q digit quarterly display value: 4
WW Digit Display value in the week of the current year: 44
W Digit Display value in the week of the current month: 1
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)
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') as nowtime from dual; // convert the date to a string
Select to_char (sysdate, 'yyyy') as nowyear from dual; // obtain the year of the time
Select to_char (sysdate, 'mm') as nowmonth from dual; // obtain the month of the time
Select to_char (sysdate, 'dd') as nowday from dual; // obtain the date of the time
Select to_char (sysdate, 'hh24') as nowhour from dual; // when obtaining the time
Select to_char (sysdate, 'mi') as nowminute from dual; // obtain the time score
Select to_char (sysdate, 'ss') as nowsecond from dual; // obtain the second of the time

Select to_date ('2017-05-07 13:23:44 ', 'yyyy-mm-dd hh24: MI: ss') from dual //

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;
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. month difference
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 ('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')
Search for the number of days except Monday and seven days between and
call dbms_utility.get_time before and after respectively to subtract the result (1/100 seconds, rather than milliseconds ).

9. Find the month
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
Extract () identifies the field value of the date or interval value
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

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;

One interval in 19.5 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
D (mod (date2-date1, 365), 30) as daily.

23. the next_day function returns the date of the next week. Day is 1-7 or Sunday-Saturday. 1 indicates that Sunday
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
day 1234 five six
-----------------------------------------------------------
select (sysdate-to_date ('2017-12-03 12:55:45 ', 'yyyy-mm-dd hh24: MI: ss') * 24*60*60 from ddual
the date is returned as a day and then converted to SS
24, round [round to the nearest date] (Day: round to the nearest Sunday)
select sysdate S1,
round (sysdate) S2,
round (sysdate, 'Year') year,
round (sysdate, 'month') month,
round (sysdate, 'day ') day from dual

25. trunc [truncation to the closest date, in days], returns the date type
Select sysdate S1,
Trunc (sysdate) S2, // returns the current date, no time, minute, second
Trunc (sysdate, 'Year') year, // returns January 1, January 1 of the current year, without hour or minute
Trunc (sysdate, 'month') month, // returns the first day of the current month, no hour, minute, second
Trunc (sysdate, 'day') day // returns Sunday of the current week, no hour, minute, second
From dual

26. returns the latest date in the date list.
Select greatest ('01-January-04 ', '04-January-04', '10-February-04 ') from dual

27. Calculating the time difference
Note: The Oracle time difference is measured in days. Therefore, it is converted into months and days.
Select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: MI: ss')/2007) as spanyears from dual // Time Difference-year
Select Ceil (moths_between (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: MI: ss') as spanmonths from dual // Time Difference-month
Select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: MI: ss') as spandays from dual // Time Difference-day
Select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: MI: ss') * 24) as spanhours from dual // Time Difference-hour
Select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: MI: ss') * 24*60) as spanminutes from dual // Time Difference-minute
Select floor (to_number (sysdate-to_date ('2017-11-02 15:55:03 ', 'yyyy-mm-dd hh24: MI: ss') * 24*60*60) as spanseconds from dual // Time Difference-seconds

28. Update Time
Note: The Oracle time addition and subtraction is based on the number of days, and the change volume is set to N, so it is converted to the year, month, and day.
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'), to_char (sysdate + N * 365, 'yyyy-mm-dd hh24: MI: ss ') as newtime from dual // change time-year
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'), add_months (sysdate, n) as newtime from dual // change time-month
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'), to_char (sysdate + N, 'yyyy-mm-dd hh24: MI: ss ') as newtime from dual // change time-day
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'), to_char (sysdate + N/24, 'yyyy-mm-dd hh24: MI: ss ') as newtime from dual // change time-hour
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'), to_char (sysdate + N/24/60, 'yyyy-mm-dd hh24: MI: ss ') as newtime from dual // change time-minute
Select to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss'), to_char (sysdate + N/24/60, 'yyyy-mm-dd hh24: MI: SS ') as newtime from dual // change time-second

29. Search for the first day and last day of the month
Select trunc (sysdate, 'month')-1, 'month') first_day_last_month,
Trunc (sysdate, 'month')-1/86400 last_day_last_month,
Trunc (sysdate, 'month') first_day_cur_month,
Last_day (trunc (sysdate, 'month') + 1-1/86400 last_day_cur_month
From dual;

author: A Jian
Source: http://ajian.cnblogs.com
the copyright of this article belongs to the author and blog Park, welcome to reprint, but without the author's consent must retain this paragraph of statement, the original Article link is clearly displayed on the Article page. Otherwise, the legal liability is retained.