Date functions commonly used in Oracle

-- 1. LAST_DAY function: find the last day of the month where the specified date is located

SELECT LAST_DAY (TO_DATE ('2014-12-02 camera camera YYYYMMI DD') FROM DUAL

2. Ask what day of the week the specified date is

SELECT TO_CHAR (TO_DATE ('1989-01-05),' DAY') FROM DUAL

3. What week of this year is the appointed date?

SELECT TO_CHAR (TO_DATE ('1989-01-05),' WW') FROM DUAL

4. Ask for the specified date to be the week of this month

SELECT TO_CHAR (TO_DATE ('1989-01-05) camera YYYYMMMI DD'),' W') FROM DUAL

-- 5. MONTHS_BETWEEN function: used to find the number of months between two dates. The result may be a decimal.

SELECT MONTHS_BETWEEN (TO_DATE ('1989-02-06)), TO_DATE (' 1989-01-05)) FROM DUAL

-- 6. The NEXT_DAY function, with the second parameter 1 for the next Sunday, 2 for Monday, 6 for Friday, 7 for Saturday / you can also use Monday-Sunday or the abbreviation / Mon-Sun

SELECT NEXT_DAY (SYSDATE,'2') FROM DUAL

Note: if you use MONDAY parameters, you need to modify the date language format first.

ALTERSESSIONSET NLS_DATE_LANGUAGE='AMERICAN'

SELECT NEXT_DAY (SYSDATE,'MONDAY') FROM DUAL

-- in Chinese language format, you can also use the day of the week to query.

ALTERSESSIONSET NLS_DATE_LANGUAGE='SIMPLIFIED CHINESE'

SELECT NEXT_DAY (SYSDATE,' Sunday') FROM DUAL

-- 7. ADD_MONTHS function: increase the month to find the date

SELECT ADD_MONTHS (TO_DATE (2000-01-29) FROM DUAL

-- 8. TREUC function: truncates to the nearest date (in days), returns the date type, and ignores it if the date contains hours, minutes and seconds

SELECT TRUNC (TO_DATE ('1989-01-05 11 lav 27 purl 35 mm')) FROM DUAL

-- 9. ROUND function: if the specified date exceeds the 15th of the current month, the 1st of the next month is displayed, otherwise the 1st of the month is displayed

SELECT ROUND (TO_DATE ('2000-01-09) camera YYYYMMMI DD'),' MONTH') FROM DUAL