Date examples:

SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUAL

Date format
Format Control Description
YYYY, YYY and YY represent four, three and two digit digital years respectively.
YEAR spelling
MM Digital Month
MONTH Monthly Combination
Abbreviation of MON Month
DD Digital Day
DAY Week
Abbreviation of DY Week
AM means morning or afternoon.
HH24, HH12 12 12 hour system or 24 hour system
MI minutes
SS seconds
Spelling of SP Numbers
Ordinal Numbers of TH Numbers

Date description:
When the input parameters corresponding to HH, MI and S S are omitted, Oracle uses 0 as the DEFAULT value. If the input date data ignores the time part, Oracle will set the time, minutes and seconds part to 0, that is to say, it will take the whole day.

Similarly, ignoring the DD parameter, Oracle will use 1 as the default value of the day, that is to say, it will take the whole month.

However, don't be confused by this inertia. If MM parameters are ignored, Oracle will not take the whole year to the current month.

Be careful:
1. When using Oracle's to_date function to do date conversion, it may intuitively use the format of "yyyy-MM-dd HH:mm:ss" as the format for conversion, but it will cause errors in Oracle: "ORA 01810 format code appears twice". Such as:

Select to_date ('2005-01-01 13:14:20','yyyyyyyyy-MM-dd HH24:mm:ss') from dual; the reason is that the SQL is case-insensitive, MM and mm are considered to be the same format code, so Oracle's SQL uses Mi instead of minutes. Select to_date ('2005-01-01 13:14:20','yyyyy-MM-dd HH24:mi:ss') from dual;

2. In addition, it should be displayed 24 hours in the form of HH24.

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi is a minute
select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm will show month

Example:

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //Date to string   
select to_char(sysdate,'yyyy') as nowYear   from dual;   //Year of time acquisition   
select to_char(sysdate,'mm')    as nowMonth from dual;   //Month of time acquisition   
select to_char(sysdate,'dd')    as nowDay    from dual;   //Date of time acquisition   
select to_char(sysdate,'hh24') as nowHour   from dual;   //Time acquisition   
select to_char(sysdate,'mi')    as nowMinute from dual;   //Acquisition of time points   
select to_char(sysdate,'ss')    as nowSecond from dual;   //Take the seconds of time 

2. Ask for a day of the week.

 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;      
   //Monday      
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;      
   monday      
   //Setting Date Language      
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';      
   //It could be the same.      
   TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')     

3. Number of days between two dates

select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;