Introduction to the TO_DATE format in Oracle

TO_DATE format (take the time: 2007-11-02:45:25 as an example)

Year:
Yytwodigits two-year display value: 07
Yyythreedigits: 007
Yyyyfourdigits four-digit year display value: 2007

Month:
Mmnumber: 11
Monabbreviated Character Set indicates the displayed value: January 1, November. If it is an English version, nov is displayed.
Monthspelledout Character Set indicates the displayed value: January 1, November. If the English version is used, November is displayed.

Day:
Ddnumber display value for the day of the month: 02
The displayed value of dddnumber in the day of the current year: 02
Short display value of dyabbreviated for the day of the week: Friday. If the English version is used, fri is displayed.
Dayspelledout: Display value for the day of the week: Friday. If the English version is used, display Friday.
Ddspthspelledout, ordinaltwelfth

Hour:
Hhtwodigits12: 01
Hh24twodigits24: 13

Minute:
Mitwodigits60 hexadecimal value: 45

Second:
Sstwodigits60 hexadecimal value: 25

Others
Qdigit quarterly display value: 4
Display value of the week of the year for WWdigit: 44
Number of weeks of the current month for Wdigit: 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)

Selectto_char (sysdate, 'yyyy-mm-ddhh24: mi: ss') asnowTimefromdual; // convert date to string
Selectto_char (sysdate, 'yyyy') asnowYearfromdual; // obtain the year of the time
Selectto_char (sysdate, 'mm') asnowMonthfromdual; // obtain the month of the time
Selectto_char (sysdate, 'dd') asnowDayfromdual; // obtain the date of the time
Selectto_char (sysdate, 'hh24') asnowHourfromdual; // when obtaining the time
Selectto_char (sysdate, 'mi') asnowMinutefromdual; // obtain the time score
Selectto_char (sysdate, 'ss') asnowSecondfromdual; // obtain the second of the time
Selectto_date ('2017-05-2004: 23: 44', 'yyyy-mm-ddhh24: mi: ss') fromdual //
2.
Selectto_char (to_date (222, 'J'), 'jsp ') fromdual

Display TwoHundredTwenty-Two
3. Check the day of the week.
Selectto_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day') fromdual;
Monday
Selectto_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUAGE = American') fromdual;
Monday
Set the date language
ALTERSESSIONSETNLS_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
Selectfloor (sysdate-to_date ('20140901', 'yyyymmdd') fromdual;

5. Use a time of null
Selectid, active_datefromtable1
UNION
Select1, TO_DATE (null) fromdual;

Note that TO_DATE (null) is used)
6. month difference
A_datebetweento_date ('20140901', 'yyyymmdd') andto_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'
AltersystemsetNLS_DATE_LANGUAGE = American
AltersessionsetNLS_DATE_LANGUAGE = American
Or write it in to_date.
Selectto_char (to_date ('1970-08-26 ', 'yyyy-mm-dd'), 'day', 'nls _ DATE_LANGUAGE = American') fromdual;
Note that I just mentioned NLS_DATE_LANGUAGE. Of course there are many more,
Available
Select * fromnls_session_parameters
Select * fromV $ NLS_PARAMETERS
8.
Selectcount (*)
From (selectrownum-1rnum
Fromall_objects
Whererownum <= to_date ('2017-02-28 ', 'yyyy-mm-dd')-to_date ('2017-
02-01 ', 'yyyy-mm-dd') + 1
)
Whereto_char (to_date ('1970-02-01 ', 'yyyy-mm-dd') + rnum-1, 'D ')
Notin ('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. Find the month
Selectmonths_between (to_date ('01-31-1999 ', 'Mm-DD-YYYY'), to_date ('12-31-1998 ', 'Mm-DD-YYYY') "MONTHS" FROMDUAL;
1
Selectmonths_between (to_date ('02-01-1999 ', 'Mm-DD-YYYY'), to_date ('12-31-1998 ', 'Mm-DD-YYYY') "MONTHS" FROMDUAL;
1.03225806451613

10. Next_day usage
Next_day (date, day)

Monday-Sunday, forformatcodeDAY
Mon-Sun, forformatcodeDY
1-7, forformatcodeD
11
Selectto_char (sysdate, 'hh: mi: ss') TIMEfromall_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.
Createorreplacefunctionsys_datereturndateis
Begin
Returnsysdate;
End;

Selectto_char (sys_date, 'hh: mi: ss') fromall_objects;

12. Hours
Extract () identifies the field value of the date or interval value
SELECTEXTRACT (HOURFROMTIMESTAMP '2017-02-2001: 38: 40') fromoffer
SQL> selectsysdate, to_char (sysdate, 'hh') fromdual;

SYSDATETO_CHAR (SYSDATE, 'hh ')
-----------------------------------------
2003-10-1319: 35: 2107

SQL> selectsysdate, to_char (sysdate, 'hh24') fromdual;

SYSDATETO_CHAR (SYSDATE, 'hh24 ')
-------------------------------------------
2003-10-1319: 35: 2119

13. Processing of the year, month, and day
Selectolder_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 (
Selecthiredateolder_date, add_months (hiredate, rownum) + rownumnewer_date
Fromemp
)
)
14. How to handle the uncertainty of the number of days in a month
Selectto_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) fromdual

16. Find the number of days this year
Selectadd_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') fromdual
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
'Yyyy99to _ C
-----------
Yyyy990099
Rrrr991999
Yyyy010001
Rrrr012001

18. Processing in different time zones
Selectto_char (NEW_TIME (sysdate, 'gmt', 'est '), 'dd/mm/yyyyhh: mi: ss'), sysdate
Fromdual;

One interval in 19.5 seconds
SelectTO_DATE (FLOOR (TO_CHAR (sysdate, 'ssss')/300) * 300, 'ssss'), TO_CHAR (sysdate, 'ssss ')
Fromdual
2002-11-19: 55: 0035786
SSSSS indicates five seconds

20. The day of the year
SelectTO_CHAR (SYSDATE, 'ddd '), sysdatefromdual
3102002-11-610: 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)
Fromdual
)

Select * fromtabname
Orderbydecode (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 Sunday
Next_day (sysdate, 6) is the next Friday from the current start. The following number is counted from Sunday.
1234567
May 5, 1234

---------------------------------------------------------------

Select (sysdate-to_date ('1970-12-2003: 55: 45', 'yyyy-mm-ddhh24: mi: ss') * 24*60*60 fromddual
The date is returned and converted to ss

24, round [round to the nearest date] (day: round to the nearest Sunday)
SelectsysdateS1,
Round (sysdate) S2,
Round (sysdate, 'Year') year,
Round (sysdate, 'month') month,
Round (sysdate, 'day') DAYfromdual

25. trunc [truncation to the closest date, in days], returns the date type
SelectsysdateS1,
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
Fromdual

26. returns the latest date in the date list.
Selectgreatest ('01-January-04 ', '04-January-04', '10-February-04 ') fromdual

27. Calculating the time difference
Note: The oracle time difference is measured in days. Therefore, it is converted into months and days.

Selectfloor (to_number (sysdate-to_date ('1970-11-02:55:03 ', 'yyyy-mm-ddhh24: mi: ss')/2007) asspanYearsfromdual // Time Difference-year
Selectceil (moths_between (sysdate-to_date ('1970-11-02:55:03 ', 'yyyy-mm-ddhh24: mi: ss') asspanMonthsfromdual // Time Difference-month
Selectfloor (to_number (sysdate-to_date ('1970-11-02:55:03 ', 'yyyy-mm-ddhh24: mi: ss') asspanDaysfromdual // Time Difference-day
Selectfloor (to_number (sysdate-to_date ('1970-11-02:55:03 ', 'yyyy-mm-ddhh24: mi: ss') * 24) asspanHoursfromdual // Time Difference-hour
Selectfloor (to_number (sysdate-to_date ('1970-11-02:55:03 ', 'yyyy-mm-ddhh24: mi: ss') * 24*60) asspanMinutesfromdual // Time Difference-Score
Selectfloor (to_number (sysdate-to_date ('1970-11-02:55:03 ', 'yyyy-mm-ddhh24: mi: ss') * 24*60*60) asspanSecondsfromdual // 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.
Selectto_char (sysdate, 'yyyy-mm-ddhh24: mi: ss'), to_char (sysdate + n * 365, 'yyyy-mm-ddhh24: mi: ss') asnewTimefromdual // change time-year
Selectto_char (sysdate, 'yyyy-mm-ddhh24: mi: ss'), add_months (sysdate, n) asnewTimefromdual // change time-month
Selectto_char (sysdate, 'yyyy-mm-ddhh24: mi: ss'), to_char (sysdate + n, 'yyyy-mm-ddhh24: mi: ss') asnewTimefromdual // change time-day
Selectto_char (sysdate, 'yyyy-mm-ddhh24: mi: ss'), to_char (sysdate + n/24, 'yyyy-mm-ddhh24: mi: ss') asnewTimefromdual // change time-
Selectto_char (sysdate, 'yyyy-mm-ddhh24: mi: ss'), to_char (sysdate + n/24/60, 'yyyy-mm-ddhh24: mi: ss') asnewTimefromdual // change time-Points
Selectto_char (sysdate, 'yyyy-mm-ddhh24: mi: ss'), to_char (sysdate + n/24/60/60, 'yyyy-mm-ddhh24: mi: ss ') asnewTimefromdual // change time-second

29. Search for the first day and last day of the month
SELECTTrunc (Trunc (SYSDATE, 'month')-1, 'month') First_Day_Last_Month,
Trunc (SYSDATE, 'month')-1/86400Last_Day_Last_Month,
Trunc (SYSDATE, 'month') First_Day_Cur_Month,
LAST_DAY (Trunc (SYSDATE, 'month') + 1-1/86400Last_Day_Cur_Month
FROMdual;