A detailed explanation of the use of to_date functions in Oracle

First, when using Oracle's to_date function to do date conversion, many Java programmers may directly use the format of "Yyyy-mm-dd HH:mm:ss" as a format for conversion, but will cause errors in Oracle: "ORA 01810 The format code appears two times. Such as:

Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mm:ss ') from dual;
The reason is that SQL is case-insensitive, mm and mm are considered the same format code, so Oracle SQL uses MI instead of minutes.

Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;

Two, another 24 hours to show the form to use HH24

Select To_char (sysdate, ' Yyyy-mm-dd HH24:mi:ss ') from Dual;//mi is minute
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mm:ss ') from DUAL;//MM displays the month

Date Format parameter meaning description

D the week of the week

Day's name, padding to 9 characters with a space

Day ordinal of DD month

The first day of the DDD year

The abbreviated name of DY Day

IW the first weeks of the year in the ISO standard

IYYY ISO standard four-bit year

YYYY four-bit year

The last three digits of the yyy,yy,y year, two digits, a

HH hours, by 12 hour meter

HH24 hours, by 24 hour meter

MI min

SS seconds

MM Month

Shorthand for Mon Month

Full name of Month month

W the first weeks of the month

The first few weeks of the WW year

1. Date-time interval operation

Current time minus 7 minutes of time

Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual

The current time minus 7 hours of time

Select Sysdate-interval ' 7 ' hour from dual

The current time minus 7 days

Select Sysdate-interval ' 7 ' from dual

Current time minus July time

Select Sysdate,sysdate-interval ' 7 ' month from dual

The current time minus 7 years time

Select Sysdate,sysdate-interval ' 7 ' from dual

Time interval multiplied by a number

Select Sysdate,sysdate-8 *interval ' 2 ' hour from dual

2. Date to character operation

Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' Yyyy-mm-dd hh:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' yyyy-ddd hh:mi:ss ') from dual

Select Sysdate,to_char (sysdate, ' yyyy-mm iw-d hh:mi:ss ') from dual

Refer to Oracle's related documentation (ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4. htm#48515)

3. Character to date operation

Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual

The specific usage is similar to the to_char above.

4. Use of Trunk/round function

Select Trunc (Sysdate, ' year ') from dual

Select Trunc (sysdate) from dual

Select To_char (trunc (sysdate, ' YYYY '), ' YYYY ') from dual

5.oracle data type with millisecond level

Back to the current time, days, minutes, seconds, milliseconds.

Select To_char (Current_timestamp (5), ' dd-mon-yyyy HH24:MI:SSxFF ') from dual;

Returns the second millisecond of the current time, specifying the precision after the second (maximum =9)

Select To_char (Current_timestamp (9), ' Mi:ssxff ') from dual;

6. Calculation program Running time (MS)

Declare

Type RC is REF CURSOR;

L_RC RC;

L_dummy All_objects.object_name%type;

L_start number default dbms_utility.get_time;

Begin

For I in 1. 1000

Loop

Open L_RC for

' Select object_name from All_objects '

' WHERE object_id = ' I;

Fetch L_RC into l_dummy;

Close L_RC;

End Loop;

Dbms_output.put_line

(Round (Dbms_utility.get_time-l_start)/100, 2)

' seconds ... ');

End

1. Date and character conversion function usage (to_date,to_char)
        
Select To_char (sysdate , ' Yyyy-mm-dd hh24:mi:ss ') as Nowtime from dual;  //date into string   
Select To_char (sysdate, ' yyyy ') As nowyear   from dual;  //acquisition time of the year   
Select To_char (sysdate, ' mm ')      as Nowmonth from dual;  //Get Time month   
Select To_char (sysdate, ' DD ')     as nowday    from dual;  //Get time   
Select To_char (sysdate, ' hh24 ') as Nowhour    from dual;  //time of acquisition   
Select To_char (sysdate, ' mi ')     as Nowminute from dual;  //Get time   
Select To_char (sysdate, ' SS ')     as Nowsecond From dual;  //Get time of the second
   
Select To_date (' 2004-05-07 13:23:44 ', ' yyyy-mm-dd hh24:mi: SS ')     from dual//

2.
Select To_char (to_date (222, ' J '), ' JSP ') from dual

Show two hundred twenty-two

3. How many days is the 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
Set Date language
ALTER session SET nls_date_language= ' American ';
I can do that.
To_date (' 2002-08-26 ', ' yyyy-mm-dd ', ' nls_date_language = American ')

4. Number of days between two dates
Select Floor (sysdate-to_date (' 20020405 ', ' YYYYMMDD ')) from dual;

5. The use of time as null
Select ID, active_date from table1
UNION
Select 1, to_date (null) from dual;

Note to use to_date (NULL)

6. Month Difference
A_date between To_date (' 20011201 ', ' YYYYMMDD ') and to_date (' 20011231 ', ' YYYYMMDD ')
The December 31 is not included in this range until after 12 o'clock noon and December 1 of 12 points.
So, when time needs to be accurate, feel to_char is still necessary

7. Date format conflict issues
The format you enter depends on the type of Oracle character set you have installed, such as: Us7ascii, the date format type is: ' 01-jan-01 '
alter system Set Nls_date_language = American
Alter session Set Nls_date_language = American
or write in To_date.
Select To_char (to_date (' 2002-08-26 ', ' yyyy-mm-dd '), ' Day ', ' nls_date_language = American ') from dual;
Notice I'm just lifting up the nls_date_language, and of course there's plenty,
To view
SELECT * FROM Nls_session_parameters
SELECT * FROM V$nls_parameters

8.     
   Select COUNT (*)      
    from (select Rownum-1 rnum     
       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 ') +rnum-1, ' D ')       
        Not in (' 1 ', ' 7 ')      
& nbsp; 
   find days between 2002-02-28 and 2002-02-01 except Monday and seven      
   The dbms_utility are called before and after each other. Get_time to subtract the result (get 1/100 seconds instead of milliseconds).     

9. Find the Month
Select Months_between (to_date (' 01-31-1999 ', ' mm-dd-yyyy '), to_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "months" from DUAL;
1
Select Months_between (to_date (' 02-01-1999 ', ' mm-dd-yyyy '), to_date (' 12-31-1998 ', ' mm-dd-yyyy ')) "months" from DUAL;
1.03225806451613

Usage of Next_day
Next_day (date, day)

Monday-sunday, for Format code day
Mon-sun, for format code DY
1-7, for format code D

11
Select To_char (sysdate, ' hh:mi:ss ') time from all_objects
Note: The time of the first record is the same as the last line
You can create a function to handle this problem
Create or Replace function sys_date return date is
Begin
return sysdate;
End

Select To_char (sys_date, ' Hh:mi:ss ') from all_objects;

12. Hours of Access
Extract () To find the field value of a date or interval value
SELECT EXTRACT (HOUR from TIMESTAMP ' 2001-02-16 2:38:40 ') from offer
Sql> Select Sysdate, To_char (sysdate, ' hh ') from dual;

Sysdate to_char (sysdate, ' HH ')
-------------------- ---------------------
2003-10-13 19:35:21 07

Sql> Select Sysdate, To_char (sysdate, ' hh24 ') from dual;

Sysdate to_char (sysdate, ' HH24 ')
-------------------- -----------------------
2003-10-13 19:35:21 19

13. The processing of the day and month
Select Older_date,
Newer_date,
Years
Months
Abs
Trunc
newer_date-
Add_months (older_date,years*12+months)
)
) days

From (select
Trunc (Months_between (newer_date, older_date)/12) YEARS,
MoD (trunc (Months_between (Newer_date, older_date)), months,
Newer_date,
Older_date
From (
Select HireDate older_date, Add_months (hiredate,rownum) +rownum newer_date
From EMP
)
)

14. Method of dealing with the indefinite number of days of month
Select To_char (Add_months (Last_day (sysdate) +1,-2), ' YYYYMMDD '), Last_day (sysdate) from dual

16. Find out the number of days this year
Select Add_months (trunc (Sysdate, ' year ')-Trunc (Sysdate, "year") from dual

How to deal with leap years
To_char (Last_day (to_date ' | |: Year, ' mmyyyy '), ' DD ')
If it's 28, it's not a leap year.