Detailed use example of to_date in oracle (oracle date format conversion)

TO_DATE format (take time: 13:45:25 as an example)

1. Date and character conversion function usage (to_date, to_char)

Copy codeThe Code is as follows:
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

2. Conversion of strings and time
Copy codeThe Code is as follows:
Select to_date ('2017-05-07 13:23:44 ', 'yyyy-mm-dd hh24: mi: ss') from dual
Select to_char (to_date (222, 'J'), 'jsp ') from dual // display Two Hundred Twenty-Two

3. Check the day of the week.
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
Select floor (sysdate-to_date ('20140901', 'yyyymmdd') from dual;

5. Use a time of null
Copy codeThe Code is as follows:
Select id, active_date from table1
UNION
Select 1, TO_DATE (null) from dual; // note that TO_DATE (null) is used)

6. month difference
Copy codeThe Code is as follows:
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, to_char is necessary when the time needs to be accurate.

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'
Copy codeThe Code is as follows:
Alter system set NLS_DATE_LANGUAGE = American
Alter session set NLS_DATE_LANGUAGE = American
// Or in to_date
Select to_char (to_date ('2017-08-26 ', 'yyyy-mm-dd '),
'Day', 'nls _ DATE_LANGUAGE = American ') from dual;
// Note that I only use NLS_DATE_LANGUAGE. Of course, there are many more. You can view
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS

8. query Special Condition days
Copy codeThe Code is as follows:
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 ')

// Search for the days from to except Monday and seven
// Call DBMS_UTILITY.GET_TIME to subtract the result (1/100 seconds instead of milliseconds)

9. Find the month
Copy codeThe Code is as follows:
Select months_between (to_date ('01-31-1999 ', 'Mm-DD-YYYY '),
To_date ('12-31-1998 ', 'Mm-DD-YYYY') "MONTHS" from dual;
// Result: 1
Select months_between (to_date ('02-01-1999 ', 'Mm-DD-YYYY '),
To_date ('12-31-1998 ', 'Mm-DD-YYYY') "MONTHS" from dual;
// Result: 1.03225806451613

10. Next_day usage
Copy codeThe Code is as follows:
Next_day (date, day)
Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D

11. Hours
Copy codeThe Code is as follows:
// Extract () identifies the field value of the date or interval value
Select extract (hour from timestamp '2017-02-16 2:38:40 ') from offer
Select sysdate, to_char (sysdate, 'hh') from dual;

SYSDATE TO_CHAR (SYSDATE, 'hh ')
-----------------------------------------
2003-10-13 19:35:21 07

Select sysdate, to_char (sysdate, 'hh24') from dual;

SYSDATE TO_CHAR (SYSDATE, 'hh24 ')
-------------------------------------------
19:35:21 19

12. Processing of the year, month, and day
Copy codeThe Code is as follows:
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
)
)

13. methods for handling the uncertainty of the number of days in a month
Copy codeThe Code is as follows:
Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

14. Find the number of days this year
Copy codeThe Code is as follows:
Select add_months (trunc (sysdate, 'Year'), 12)-trunc (sysdate, 'Year') from dual
// Method for processing a leap year
To_char (last_day (to_date ('02 '|: year, 'mmyyyy'), 'dd ')
// If it is 28, it is not a leap year

15. Differences between yyyy and rrrr

Copy codeThe Code is as follows:
YYYY99 TO_C
-----------
Yyyy 99 0099
Rrrr 99 1999
Yyyy 01 0001
Rrrr 01 2001

16. Processing in different time zones
Copy codeThe Code is as follows:
Select to_char (NEW_TIME (sysdate, 'gmt', 'est '), 'dd/mm/yyyy hh: mi: ss '),
Sysdate from dual;

17. One interval in five seconds
Copy codeThe Code is as follows:
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

18. The day of the year
Copy codeThe Code is as follows:
Select TO_CHAR (SYSDATE, 'ddd '), sysdate from dual
// 310 10:03:51

19. Calculation hour, minute, second, millisecond
Copy codeThe Code is as follows:
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
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.

20. next_day Function
Copy codeThe Code is as follows:
// Return the date of the next week. day is 1-7 or Sunday-Saturday, and 1 indicates 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
// November 5, 1234
Select (sysdate-to_date ('2017-12-03 12:55:45 ', 'yyyy-mm-dd hh24: mi: ss') * 24*60*60 from dual
// The date is returned and then converted to ss

21, round [round to the nearest date] (day: round to the nearest Sunday)
Copy codeThe Code is as follows:
Select sysdate S1,
Round (sysdate) S2,
Round (sysdate, 'Year') year,
Round (sysdate, 'month') month,
Round (sysdate, 'day') day from dual

22, trunc [truncation to the closest date, in days], returns the date type

Copy codeThe Code is as follows:
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

23. returns the latest date in the date list.

Copy codeThe Code is as follows:
Select greatest ('01-January-04 ', '04-January-04', '10-February-04 ') from dual

24. Calculating Time Difference
Copy codeThe Code is as follows:
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')/365) 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-second

25. Update Time
Copy codeThe Code is as follows:
// 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/60, 'yyyy-mm-dd hh24: mi: ss') as newTime from dual // change time-seconds

26. Search for the first day and last day of the month
Copy codeThe Code is as follows:
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;