Oracle Date-Time function Usage Summary _oracle

Today free to do nothing, deliberately collated some information from the Internet for future inspection.

First, the usual date data format

1. Get the last, two, three, four-digit year

Select To_char (sysdate, ' Y ') from dual; --Get the last one of the year
Select To_char (sysdate, ' YY ') from dual; --Get the last two digits of the year
Select To_char (sysdate, ' YYY ') from dual; --Get the last three digits of the year
Select To_char (sysdate, ' YYYY ') from dual; --Get the last four digits of the year

2. Get current quarter
Select To_char (sysdate, ' Q ') from dual; --1 ~ March is the first quarter, 2 indicates the second quarter.

3. Get the number of months
Select To_char (sysdate, ' MM ') from dual; --May to 05

4. Access to the Roman representation of the month
Select To_char (sysdate, ' RM ') from dual; --May as V

5. Get the month name that is represented by a length of 9 characters
Select To_char (sysdate, ' Month ') from dual; --May for May

6. Get the first few weeks of the year
Select To_char (sysdate, ' WW ') from dual; --May 20, 2014 20th Week of 2014

7. Get the first few weeks of this month
Select To_char (sysdate, ' W ') from dual; --May 20, 2014 3rd week of May

8. Get the day of the year
Select To_char (sysdate, ' DDD ') from dual; --May 20, 2014 is the 140th day of 2014

9. Get the day ordinal of the month
Select To_char (sysdate, ' DD ') from dual; --May 20, 2014 is the 20th day of May

10. Get the day of the week
Select To_char (sysdate, ' D ') from dual; -May 20, 2014 is the third day of the week (since Sunday)

11. Week to get Chinese
Select To_char (sysdate, ' DY ') from dual; --May 20, 2014 for Tuesday

12. Get 12 number of hours in the system
Select To_char (sysdate, ' HH ') from dual; --22:36 minutes with 12-hour clock to 10 points

13. Get 24 number of hours in the system
Select To_char (sysdate, ' HH24 ') from dual; --22:36 minutes with 24-hour clock to 22 points

Second, commonly used time function

1. trunc (d, [?])

Copy Code code as follows:

Select Sysdate S1--Returns the current date, sometimes seconds
Trunc (sysdate) S2--Return to current date, no time seconds
Trunc (Sysdate, ' year '),--Return to the January 1 of the current years, no time and seconds
Trunc (sysdate, ' Month ') month,--return to the 1st of the current month, no time seconds
Trunc (sysdate, ' Day ') day,--return to the current week of Sunday, no time seconds
Trunc (sysdate, ' Q ') quarter,--return to the current quarter of 1st, no time and seconds
Trunc (sysdate, ' D ') WEEK--return to the current week of Sunday, no time and seconds
From dual

2. Round (d, [?]) rounded to the nearest date

Copy Code code as follows:

Select Sysdate S1,
Round (sysdate) S2,
Round (sysdate, ' year '),--rounded to the nearest 2014/1/1
Round (sysdate, ' Month ') month,--rounded to the nearest month 2014/6/1
Round (sysdate, ' Day ') day--rounded to the nearest Sunday 2014/5/18
From dual

3. Last_day (d) Obtain the date of the last day of the month containing D

Select Last_day (sysdate) from dual; --get last day of this month: 2014/5/31 22:46:01
4. Add_months (d, N) Date D push back n months

Select Add_months (sysdate,2) from dual; --date pushed back 2 months: 2014/7/20 22:49:36

5. Next_day (d, Day)

Select Next_day (sysdate,2) from dual; --In the first week after the date sysdate, what date is the 2nd day of the specified week

6. Months_between (f,s) date between F and S number of months

Select Months_between (sysdate,to_date (' 2007-04-12 ', ' Yyyy-mm-dd ')) from dual; --85.2889874551971

7. Get the number of days between two dates

Select Floor (sysdate-to_date (' 20140405 ', ' YYYYMMDD ')) from dual;

Iii. Comprehensive usage

1. Get last day of last month

Select To_char (Add_months (Last_day (sysdate), -1), ' Yyyy-mm-dd ') Lastday from dual;

2. Get today of last month

Select To_char (Add_months (sysdate,-1), ' Yyyy-mm-dd ') pretoday from dual;

3. Get the first day of last month

Select To_char (Add_months (Last_day (sysdate) +1,-2), ' Yyyy-mm-dd ') FirstDay from dual;

4. Obtain a specific date for all Friday of the month

Copy Code code as follows:

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 ';

5. Find the number of days between 2002-02-28 and 2002-02-01 except Monday and seven

Copy Code code as follows:

Select COUNT (*)
From (select Rownum-1 row_num
From All_objects
where RowNum <= to_date (' 2002-02-28 ', ' yyyy-mm-dd ')-
To_date (' 2002-02-01 ', ' yyyy-mm-dd ') + 1)
where To_char (to_date (' 2002-02-01 ', ' yyyy-mm-dd ') + row_num-1, ' D ') Not in (' 1 ', ' 7 ')