To_date Detailed Usage example in Oracle (Oracle date format conversion)

This article focuses on the detailed usage examples of to_date in Oracle, including the use of period and character conversion functions, string and time conversions, the day of the week, the number of days between two dates, and the use of months.

To_date format (time: 2007-11-02 13:45:25 for example)

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 converted to string  select To_char (sysdate, ' yyyy ') as  nowyear from   dual;   Gets the year of the time  select To_char (sysdate, ' mm ') as    nowmonth from  dual;   Gets the month of the time  select To_char (sysdate, ' DD ') as    nowday from    dual;   Get time of day  select To_char (sysdate, ' hh24 ') as  nowhour from   dual;   Gets the time of the  select To_char (sysdate, ' mi ') as    nowminute from dual;   Gets the time of the sub  Select To_char (sysdate, ' SS ') as    nowsecond from dual;   Gets the seconds of the time

2. String and Time transfer

Select To_date (' 2004-05-07 13:23:44 ', ' yyyy-mm-dd hh24:mi:ss ') from Dualselect To_char (to_date (222, ' J '), ' JSPs ') from Dual//Show hundred Twenty-two    

3. What 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 ';     can also be so     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 null

Select ID, active_date from table1     UNION     Select 1, to_date (null) from dual;  

6. Month Difference

A_date between To_date (' 20011201 ', ' YYYYMMDD ') and to_date (' 20011231 ', ' YYYYMMDD ')     // It is not included in this range until after 12 o'clock noon on December 31 and before 12 o ' December 1.     //So, when time needs to be accurate, think To_char is still necessary

7. Date format conflict issues
The input format depends on the type of Oracle character set you installed, such as: Us7ascii, the type of date format 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 nls_date_language and of course there's a lot more to see     select * FROM Nls_session_parameters     select * from V$nls_parameters    

8. Query Special Condition Days

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 ')     //Find the number of days between 2002-02-28 and 2002-02-01, except Monday and seven     //Call dbms_utility respectively before and after. Get_time, let's 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;     The result is: 1     Select Months_between (to_date (' 02-01-1999 ', ' mm-dd-yyyy '), to_date (' 12-31-1998 ', ' mm-dd-yyyy ')) " MONTHS "from DUAL;     The result is: 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. Acquisition of Hours

Extract () Find the field value of the date or interval value Select Extract (HOUR from TIMESTAMP ' 2001-02-16 2:38:40 ') from     the offer select Sysdate, To_char ( Sysdate, ' HH ') from dual;     Sysdate               to_char (sysdate, ' HH ')     -----------------------------------------     2003-10-13 19:35:21   07    Select Sysdate, To_char (sysdate, ' hh24 ') from dual;     Sysdate               to_char (sysdate, ' HH24 ')     -------------------------------------------     2003-10-13 19:35:21   19   

12. Treatment of the month and day

SELECT  older_date,  newer_date,  years,  months,  ABS      (TRUNC (Newer_date-add_months ( Older_date, Years * + months))  daysfrom  (    SELECT      TRUNC        (Months_between, older_date)/      years,      MOD (TRUNC (          months_between (newer_date, Older_date)        ),        12      ) MONTHS,      newer_date,      older_date    from      (        SELECT          hiredate older_date,          add_months (HireDate, ROWNUM) + ROWNUM newer_date        from          emp      )  )   

13. Ways to deal with the indefinite number of months

Select To_char (Add_months (Last_day (sysdate) +1,-2), ' YYYYMMDD '), Last_day (sysdate) from dual    

14. Find out the number of days this year

Select Add_months (trunc (Sysdate, ' year '), and-trunc (Sysdate, ' year ')     the From dual//Leap year Processing method     To_char (Last_day (to _date (' in '    | |: Year, ' mmyyyy '), ' DD ')      

The difference between 15.yyyy and rrrr

YYYY99  to_c     -----------     yyyy 0099 RRRR, 1999 yyyy 0001 rrrr    01 2001  

16. Processing in different time zones

Select To_char (New_time (sysdate, ' GMT ', ' EST '), ' dd/mm/yyyy hh:mi:ss '), sysdate from   dual;   

17.5 Seconds an interval

Select to_date (Floor (To_char (sysdate, ' sssss ')/300) *, ' sssss '), To_char (sysdate, ' sssss ') from   dual    // 2002-11-1 9:55:00 35786     //sssss represents 5-bit seconds    

18. The day ordinal of a year

Select To_char (sysdate, ' DDD '), sysdate from dual   //310  2002-11-6 10:03:51    

19. Calculate hours, minutes, seconds, milliseconds

SELECT days   ,   A,   TRUNC (A *) Hours,   TRUNC (A * * 60-60 * TRUNC (A *)) Minutes,   TRUNC (     a  * 60-60 * TRUNC (A * +)   ) Seconds, TRUNC (A * $ * * * *     100-100 * TRUNC (A * 24 * 60) *)   mseconds from   (     SELECT       TRUNC (sysdate) days,       Sysdate-trunc (sysdate) A     from       dual   ) SELECT     *   from     tabname   ORDER by     DECODE (MODE, ' FIFO ', 1,-1) * TO_CHAR (RQ, ' Yyyymmddhh24miss ') Floor   ((date2-date1)/365) as the year     //Floor  ((date2-date1, 365)/30) as the month     //  D (MoD (date2-date1 , 365), 30) as the day.

20.next_day function

Returns the date of next week, day is 1-7 or Sunday-Saturday, 1 means Sunday next_day (sysdate,6) is from the current start of the next Friday. The following numbers are counted from Sunday onwards.     //1  2  3  4  5  6  7     //day 123456   Select (Sysdate-to_date (' 2003-12-03 12:55:45 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60 from dual//date returned is natural after conversion to SS

21,round[rounded to the nearest date] (day: rounded to the nearest Sunday)

Select Sysdate s1,round (sysdate) S2, round (sysdate, ' year ') Year,round (sysdate, ' Month ') month, round (sysdate, "Day") Day From dual

22,trunc[truncated to the nearest date, in days], the date type is returned

Select Sysdate S1,                    trunc (sysdate) S2,                 //returns the current date, minutes and seconds trunc (Sysdate, ' year ')        of year,//returns the current January 1, minutes and seconds trunc ( Sysdate, ' month ') month,     //Return to the current month of 1st, Trunc (sysdate, ' Day ') Day           //Return to the current week of Sunday, no time and seconds from dual

23, returns the latest date in the list of dates

Select Greatest (' January-January-04 ', ' April-January-04 ', ' October-February -04 ') from dual

24. Calculate the time difference

Note: Oracle time difference is in days, so convert to month, day Select Floor (To_number (sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ') )/365) as Spanyears from dual        //Time difference-year Select Ceil (Moths_between (sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd Hh24:mi:ss ')) as spanmonths from dual           //Time difference-month Select floor (to_number (sysdate-to_date (' 2007-11-02 15:55:03 ', ' Yyyy-mm-dd Hh24:mi:ss ')) as spandays from dual             //Time difference-days Select Floor (To_number (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24) as spanhours from dual         //Time difference-when Select floor (To_number (' Sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60) as spanminutes from dual    //Time difference-min Select floor (To_number ( Sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60) as Spanseconds from dual//Time Difference-sec

25. Update Time

Oracle time plus minus is the number of days, set the change to N, so converted into years, day select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),   to_char (sysdate+n*365, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual        //Change Time-year select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),    add_ Months (Sysdate,n) as NewTime from dual                                 //Change Time-month select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),    to_char ( Sysdate+n, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual            //Change Time-day Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),    to_char (sysdate+n/24, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual         //Change Time-When select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss '),    to_char (sysdate+n/24/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual      // Change Time-Sub Select To_char (sysdate, ' yyyy-mm-dd hh24:mi:ss '),    to_char (sysdate+n/24/60/60, ' Yyyy-mm-dd hh24:mi:ss ') as NewTime from dual   //Change Time-Sec

26. Find the first day of the month and the last day

SELECT Trunc (Trunc (sysdate, ' month ')-1, ' month ') First_day_last_month,    Trunc (sysdate, ' month ')-1/86400 Last_day_ Last_month,    Trunc (sysdate, ' month ') First_day_cur_month,    last_day (Trunc (sysdate, ' month ')) + 1-1/86400 Last_ Day_cur_monthfrom dual;

　　

To_date Detailed Usage example in Oracle (Oracle date format conversion)