What are the Oracle date query statements?

Oracle date query statements include: 1. Get the current date to be the first week of this month, the code is [select to_char(sysdate,'W')]; 2. Get the current date to be the first week of the week How many days? The code is [select sysdate,to_char(sysdate,D].

oracle date query statements are:

1: Get the current date which is the week of this month

SQL>   select   to_char(sysdate,'YYYYMMDD   W   HH24:MI:SS')   from   dual;  
TO_CHAR(SYSDATE,'YY20030327   4   18:16:09  SQL>   
select   to_char(sysdate,'W')   from   dual;

Result:

4

2: Get the current date It is the day of the week. Note that Sunday is the first day.

SQL>   select sysdate,to_char(sysdate,'D')from dual;
  
SYSDATE       T

Result:

27-MAR-03 5

Similar to:

select   to_char(sysdate,'yyyy')   from   dual;   --年
select   to_char(sysdate,'Q'   from   dual;           --季
select   to_char(sysdate,'mm')   from   dual;       --月
select   to_char(sysdate,'dd')   from   dual;       --日

• ddd The number of days in the year

• WW The number of weeks in the year

• W The number of weeks in the month

• DAY The day of the week

• D Today’s corresponding NUMBER

• '1','Sunday', '2','Monday ', '3','Tuesday', '4','Wednesday', '5','Thursday', '6','Friday', '7','Saturday'

• hh Hours(12)

• hh24 Hours(24)

• Mi Minutes

• ss Seconds

3: Get the day of the week that the current date is Chinese display:

SQL>   select   to_char(sysdate,'day')   from   dual;
  
TO_CHAR(SYSDATE,'DAY')

Result:

Thursday

4: If a table has an index on a date type field, how to use

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

5: Get the current date

select sysdate from dual;

6: Get the date at 0:00:00:00 in the morning

select trunc(sysdate) from dual;

--Get the last second of the day

select trunc(sysdate) + 0.99999 from dual;

--Get the specific value of the hour

select trunc(sysdate) + 1/24 from dual;
select trunc(sysdate) + 7/24 from dual;

7: Get the date at 0:00:00 tomorrow morning

select trunc(sysdate+1) from dual;
select trunc(sysdate)+1 from dual;

8: Get the date of the first day of this month

select trunc(sysdate,'mm') from dual;

9: Get the date of the first day of the next month

select trunc(add_months(sysdate,1),'mm') from dual;

10: Return the last day of the current month?

select last_day(sysdate) from dual;      
select last_day(trunc(sysdate)) from dual;
select trunc(last_day(sysdate)) from dual;
select trunc(add_months(sysdate,1),'mm') - 1 from dual;

11: Get every day of the year

select trunc(sysdate,'yyyy')+ rn -1 date0
from
(select rownum rn from all_objects
where rownum<366);

12 :Today is the Nth day of this year

SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;

13: How to add 2 years to an existing date

select add_months(sysdate,24) from dual;

14: Judgment Whether the year where a certain day is located is a run year

select decode(to_char(last_day(trunc(sysdate,'y')+31),'dd'),'29','闰年','平年') from dual;

15: Determine whether it will be a run year two years later

select decode(to_char(last_day(trunc(add_months(sysdate,24),'y')+31),'dd'),'29','闰年','平年') from dual;

16: Get the date Quarterly

select ceil(to_number(to_char(sysdate,'mm'))/3) from dual;
select to_char(sysdate, 'Q') from dual;

Related learning recommendations: oracle database learning tutorial

The above is the detailed content of What are the Oracle date query statements?. For more information, please follow other related articles on the PHP Chinese website!