Orcal Date Query Settings

First, the usual date data format
1.Y or yy or yyy year's last, two-bit or three-bit
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



The 2.Q quarter was the first quarter of January-March, with 3 representing the third quarter.



Sql> Select to_char (sysdate, ' Q ') from dual;

To_char (sysdate, ' Q ')

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

3

Number of 3.MM months



Sql> Select to_char (sysdate, ' MM ') from dual;



To_char (sysdate, ' MM ')

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

09

Roman representation of the 4.RM month (ix in Roman numerals 9)

Sql> Select to_char (sysdate, ' RM ') from dual;

To_char (sysdate, ' RM ')

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

Ix

5.Month month name in 9-character length



Sql> Select to_char (sysdate, ' Month ') from dual;



To_char (sysdate, ' MONTH ')

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

September



6.WW the first weeks of the year (September 9, 2008 is the 37th Week of 2008)



Sql> Select to_char (sysdate, ' WW ') from 0dual;

To_char (sysdate, ' WW ')

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

37

7.W the week of this month (September 9, 2008 is the 2nd week of September)



Sql> Select to_char (sysdate, ' W ') from dual;

To_char (sysdate, ' W ')

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

2



8.DDD Days of the year (September 9, 2008 is the No. 253 day of 2008)



Sql> Select to_char (sysdate, ' DDD ') from dual;



To_char (sysdate, ' DDD ')

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

253

9. DD Day of the month

Sql> Select to_char (sysdate, ' DD ') from dual;

To_char (sysdate, ' DD ')

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

9



10.D Week of the first day



Sql> Select to_char (sysdate, ' D ') from dual;



To_char (sysdate, ' D ')

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

2



The day of the 11.DY in Chinese (September 9, 2008 for Tuesday)



Sql> Select to_char (sysdate, ' DY ') from dual;



To_char (sysdate, ' DY ')

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

Tuesday

12.HH or HH12 12 hours (16:09 divided into 12-hour timing for 4 points)

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

Second, commonly used time function



1.trunc (sysdate, ' Q ') first day of the quarter

Sql> Select Trunc (sysdate, ' Q ') from dual;



TRUNC (sysdate, ' Q ')

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

2007-4-1



2.trunc (sysdate, ' D ') 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 pushed 2 months later



Sql> Select Add_months (sysdate,2) from dual;



Add_months (sysdate,2)
---------------------
2007-7-29 15:21:14
5.next_day (sysdate,2) Date sysdate The first week after the 2nd (Specify day of the week) what is the date
Sql> Select Next_day (sysdate,2) from dual;
Next_day (sysdate,2)
-------------------
2007-6-4 15:22:10
6.months_between (f,s) Date F and S difference between the number of months
Sql> Select Months_between (sysdate,to_date (' 2007-04-12 ', ' YYYY-MM-DD ') from dual;

Months_between (sysdate,to_date
------------------------------
1.56909908900836
7. The month in which Sysdate+5 was received
Sql> SELECT to_char (sysdate+5, ' mon ', ' Nls_date_language=american ') from dual;
To_char (sysdate+5, ' MON ', ' nls_d
------------------------------
June
8.current_date () returns the current date in the current session time zone.

9.select Dbtimezone from dual;

10.extract () Find the field value for 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
==================================================================
Third, some practice after the use of:
1. Last month Day:
Select To_char (Add_months (Last_day (sysdate), -1), ' Yyyy-mm-dd ') Lastday from dual;
2. Today, last month
Sql> Select To_char (add_months (sysdate,-1), ' Yyyy-mm-dd ') pretoday from dual;
3. First day of last month
Sql> Select To_char (add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') FirstDay from dual;
4. To find the specific dates for all Friday of the month
SELECT to_char (B.A, ' yy-mm-dd ')
From (SELECT trunc (sysdate, ' mm ') +rownum-1 a
From Dba_objects where RowNum <) b
WHERE to_char (B.A, ' day ') = ' Friday ';
If you change the where To_char (T.D, ' mm ') = To_char (sysdate, ' mm ') to sysdate-90, that is, to find the first three months of the current month
The date of every Friday.
5. Get the current month and date of the system
Select Trunc (sysdate, ' MM ') +rownum-1 from dba_objects;
-----------------------------------
To_date String type to change date type

character in the corresponding position in the string, must conform to the time range limit

14.MI minutes (0~59)

Note the MM format should not be used for minutes (MI should be used in minutes). MM is the format for the month, and it works for minutes, but the result is wrong.