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; Convert Date to String
Select To_char (sysdate, ' yyyy ') as nowyear from dual; Get the year of time
Select To_char (sysdate, ' mm ') as nowmonth from dual; Get the month of time
Select To_char (sysdate, ' DD ') as nowday from dual; Day of getting time
Select To_char (sysdate, ' hh24 ') as nowhour from dual; When to get time
Select To_char (sysdate, ' mi ') as Nowminute from dual; Get a minute of time
Select To_char (sysdate, ' SS ') as Nowsecond from dual; Get seconds of time

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. Time NULL usage      
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 ')       
Then December 31 noon 12 o'clock and December 1 before 12 points is not included in this scope.      
So, when time needs to be precise, feel to_char is still necessary

7. Date format Conflict      
The format you enter depends on the type of Oracle character set you have 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 the nls_date_. LANGUAGE, of course, there are many,     
viewable      
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 2002-02-28 to The number of days in 2002-02-01 except Monday and seven      
calls dbms_utility separately before and after. 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.

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    
br>---------------------------------------------------------------

select    (sysdate-to_ Date (' 2003-12-03 12:55:45 ', ' yyyy-mm-dd hh24:mi:ss ')) *24*60*60 from Ddual
The dates are returned by natural conversion to SS

24,round[ Round to nearest date] (day: round to nearest Sunday)
Select Sysdate S1,
Round (sysdate) S2,
Round (sysdate, ' 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; Source: