1. Common date data formats
or YY or YYY The last one, two or three
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from dual;
TO_CHAR(SYSDATE,'YYY')
----------------------
007
Quarter January to March is the first quarter, and 2 represents the second quarter.
SQL> Select to_char(sysdate,'Q') from dual;
TO_CHAR(SYSDATE,'Q')
--------------------
2
Number of months
SQL> Select to_char(sysdate,'MM') from dual;
TO_CHAR(SYSDATE,'MM')
---------------------
05
Roman representation of the month (V is represented by 5 in Roman numerals)
SQL> Select to_char(sysdate,'RM') from dual;
TO_CHAR(SYSDATE,'RM')
---------------------
V
Month name represented by 9 characters
SQL> Select to_char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------
May
What week of that year (May 29, 2007 is the 22nd week of 2007)
SQL> Select to_char(sysdate,'WW') from dual;
TO_CHAR(SYSDATE,'WW')
---------------------
22
What week of this month (May 29, 2007 is the 5th week of May)
SQL> Select to_char(sysdate,'W') from dual;
TO_CHAR(SYSDATE,'W')
--------------------
5
What day of that year (May 29, 2007 is the 149th day of 2007)
SQL> Select to_char(sysdate,'DDD') from dual;
TO_CHAR(SYSDATE,'DDD')
----------------------
149
9. What day of the month DD
SQL> Select to_char(sysdate,'DD') from dual;
TO_CHAR(SYSDATE,'DD')
---------------------
29
What day of the week
SQL> Select to_char(sysdate,'D') from dual;
TO_CHAR(SYSDATE,'D')
--------------------
3
Chinese day of the week ((Tuesday, May 29, 2007))
SQL> Select to_char(sysdate,'DY') from dual;
TO_CHAR(SYSDATE,'DY')
---------------------
Tuesday
Or HH12 12-digit hours (16:09 is counted as 4 o'clock in the 12-hour system)
SQL> Select to_char(sysdate,'HH') from dual;
TO_CHAR(SYSDATE,'HH')
---------------------
04
13.HH24 24-hour system
SQL> Select to_char(sysdate,'HH24') from dual;
TO_CHAR(SYSDATE,'HH24')
-----------------------
16
2. Common time functions
(sysdate,'Q') Day 1 of the quarter
SQL> select trunc(sysdate,'Q') from dual;
TRUNC(SYSDATE,'Q')
------------------
2007-4-1
(sysdate,'D') The first day of the week (Sunday)
SQL> select trunc(sysdate,'D')from dual;
TRUNC(SYSDATE,'D')
------------------
2007-5-27
3.last_day(sysdate) The last day of this month
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2007-5-31 15:20:3
4.add_months(sysdate,2) Date sysdate is pushed back by 2 months
SQL> select add_months(sysdate,2) from dual;
ADD_MONTHS(SYSDATE,2)
---------------------
2007-7-29 15:21:14
5.next_day(sysdate,2) What date is the second (the day of the specified week) in the first week after the date sysdate
SQL> select next_day(sysdate,2) from dual;
NEXT_DAY(SYSDATE,2)
-------------------
2007-6-4 15:22:10
6.Months_between(f,s) The number of months difference between dates f and s
SQL> select months_between(sysdate,to_date('2007-04-12','yyyy-mm-dd'))from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
1.56909908900836
7. Get the month when SYSDATE+5 is located
SQL> SELECT to_char(SYSDATE+5,'mon','nls_date_language=american') FROM dual;
TO_CHAR(SYSDATE+5,'MON','NLS_D
------------------------------
jun
8.current_date() returns the current date in the current session time zone.
dbtimezone from dual;
() Find the field value of the date or interval value
SQL> select extract(month from sysdate) "This Month" from dual;
This Month
----------
5
SQL> select extract(year from sysdate) "This year" from dual;
This year
----------
2007
SQL> select extract(month from add_months(sysdate,2)) " Month" from dual;
Month
----------
7
==================================================================
3. Some practical usages:
1. Last month’s end:
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual；
2. Last month today
SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
3. The first day of last month
SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
4. Find the specific dates for all Fridays in a certain month
SELECT to_char(,'YY-MM-DD')
FROM ( SELECT trunc(SYSDATE,'mm')+ROWNUM-1 a
FROM dba_objects where rownum < 32 ) b
WHERE to_char(,'day')='Friday';
If you change where to_char(, 'MM') = to_char(sysdate, 'MM') to sysdate-90, it is to find the first three months of the current month
Friday date.
5. Get the current month and later date of the system
select trunc(sysdate, 'MM')+ROWNUM-1 FROM dba_objects ;
-----------------------------------
to_date string type to convert date type
Characters at corresponding positions in a string must comply with the time range limitation.
Minutes (0~59)
Tip: Be careful not to use MM format for minutes (mins should use MI). MM is a format for months, using it for minutes can also work, but the result is wrong.
Number of seconds (0~59)