Oracle to_date Format __oracle

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

Year:
YY two digits two-digit year display value: 07
YYY three digits three-digit year display value: 007
YYYY four digits four-digit year display value: 2007

Month:
MM number two-bit month display value: 11
Mon abbreviated Character Set display value: November, if the English version, display Nov
Month spelled out character Set display value: November, if the English version, display November

Day:
DD number display value for day of month: 02
DDD number Displays the value in the first days: 02
DY abbreviated when Zhou Di a few days to display the value: Friday, if the English version, display Fri
Day spelled out when Zhou Di days full Write display value: Friday, if the English version, display Friday
Ddspth spelled out, ordinal twelfth

Hour:
HH two digits 12-hour binary display value: 01
Hh24 two digits 24-hour binary display value: 13

Minute:
MI Two digits 60 binary display value: 45

Second:
SS Two digits 60 binary display value: 25

Other
Q Digit Quarterly display value: 4
WW digit display value for the first weeks of the year: 44
W Digit The week of the month display value: 1

24-hour format of the time range: 0:00:00-23:59:59 ....
12-hour format of the time range: 1:00:00-12:59:59 ....

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

Find days between 2002-02-28 and 2002-02-01 with the exception of 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.

The difference between 17.yyyy and rrrr
' YYYY99 To_c
------- ----
YYYY 99 0099
RRRR 99 1999
yyyy 01 0001
Rrrr 01 2001

18. Processing of different time zones
Select To_char (New_time (sysdate, ' GMT ', ' EST '), ' dd/mm/yyyy hh:mi:ss '), sysdate
from dual;

19.5-second clock, one 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-digit seconds

20. The first day of the year
Select To_char (sysdate, ' DDD '), sysdate from dual

310 2002-11-6 10:03:51

21. Calculate hours, minutes, seconds, milliseconds      
 select     
   days,     
  A,     
  trunc (a*24) hours,     
  trunc (A*24*60-60*trunc (a*24)) minutes,      
  trunc (A*24*60*60-60*trunc (a*24*60)) seconds,     
  trunc (A*24*60*60*100-100*trunc (a*24*60*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 day.

The

23.next_day function       Returns the date of the next week, day 1-7 or Sunday-Saturday, 1 for Sunday
   next_day (sysdate , 6) is from the current beginning of the next Friday. The figures for the following are 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 ddual
   Date returns natural, converted to SS
    
24,round[ Round to nearest date] (day: round to nearest Sunday)
 select sysdate S1,
 round (sysdate) S2,
 round (Sysdate, ' year ') Year,
 round (sysdate, ' Month ') month,
 round (sysdate, "Day") Day from dual

25,trunc[truncated to the nearest date, the Unit is day, and returns the date type
Select Sysdate S1,
Trunc (sysdate) S2,//return to current date, no time seconds
Trunc (Sysdate, ' year ') years,//return to the January 1 of the current age, no time 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
From dual

26, returns the latest date in the list of dates
Select Greatest (' January-January-04 ', ' April-January-04 ', ' October-February -04 ') from dual

27. Calculate Time Lag
Note: Oracle time difference is in the number of days, so converted into years, 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 Lag-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 Lag-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 Lag-day
Select Floor (To_number (sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24) as spanhours from dual Time Lag-When
Select Floor (To_number (sysdate-to_date (' 2007-11-02 15:55:03 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60) as Spanminutes Dual//Time lag-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 Dual//Time lag-seconds

28. Update Time
Note: Oracle time plus minus is in the number of days, set the change amount 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-minutes
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

29. Find the first day of the month, 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_month
 from dual;