oracle-Date format Conversion

To_date ("String to convert", "converted format") The format of the two parameters must match, otherwise it will be an error. That is, the first argument is interpreted in the format of the second parameter.

To_char (date, "convert format") converts the given date according to the "conversion format".

　　

Format of the To_char conversion:

Represents year's: Y represents the last 2 digits of the year yyy represents the last 3 digits of the year yyyy in 4-digit years

MM with 2 digits for month; Mon in abbreviated form such as November or Nov; month with full name such as November or November

Indicates day: DD indicates the days of the month, DDD indicates the day of the year, and Dy is in for a few days, such as Friday or Fri;day when the in is written for a few days such as Friday or Friday.

Represents hour: HH 2 digits for hour 12, hh24 2 digits for hours 24 hours

Represents minute: Mi 2 digits for minutes

Represents second: SS 2 digits for seconds 60 binary

Indicates quarterly: Q A number represents quarter (1-4)

There is also WW used to indicate the week of the year when W is used to denote the month ordinal.

　　

Time range under 24-hour system: 00:00:00-23:59:59

Time range under 12-hour system: 1:00:00-12:59:59

Like what:

　　Select To_char (sysdate, ' Yy-mm-dd hh24:mi:ss ') from dual//display: 08-11-07 13:22:42

Select To_date (' 2005-12-25,13:25:59 ', ' Yyyy-mm-dd,hh24:mi:ss ') from dual//display: 2005-12-25 13:25:59

And if the writing: Select To_date (' 2005-12-25,13:25:59 ', ' yyyy-mm-dd,hh:mi:ss ') from the dual, will be an error, because the hour HH is 12 binary, 13 is illegal input, can not match.

Add:

Current time minus 7 minutes: Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual
Current time minus 7 hours: Select Sysdate-interval ' 7 ' hour from dual
Current time minus 7 days: Select Sysdate-interval ' 7 ' date from dual
Current time minus July time: Select Sysdate,sysdate-interval ' 7 ' month from dual
Current time minus 7 years: Select Sysdate,sysdate-interval ' 7 ' year from dual
Time interval multiplied by a number: Select Sysdate,sysdate-8*interval ' 7 ' hour from dual

　　

Dual Pseudo-column

Meaning explanation:

Dual is an actual table in Oracle that can be read by any user, often in a SELECT statement block without a target table.

1. Date and character conversion function usage (TO_DATE,TO_CHAR)

SelectTo_char (Sysdate,'YYYY-MM-DD Hh24:mi:ss') asNowtime fromDual//Date converted to stringSelectTo_char (Sysdate,'yyyy') asNowyear fromDual//get the year of the timeSelectTo_char (Sysdate,'mm') asNowmonth fromDual//get the month of the timeSelectTo_char (Sysdate,'DD') asNowday fromDual//get the day of the timeSelectTo_char (Sysdate,'hh24') asNowhour fromDual//when you get the timeSelectTo_char (Sysdate,'mi') asNowminute fromDual//get the minutes of the timeSelectTo_char (Sysdate,'SS') asNowsecond fromDual//gets the seconds of the timeSelectTo_date ('2004-05-07 13:23:44','YYYY-MM-DD Hh24:mi:ss') fromDual//

2.

Select to_char (to_date (222,'J'),'Jsp'  from Dual shows the Hundred Twenty-two

3. What is the day of the week?

SelectTo_char (To_date ('2002-08-26','YYYY-MM-DD'),' Day') fromdual; MondaySelectTo_char (To_date ('2002-08-26','YYYY-MM-DD'),' Day','nls_date_language = American') fromdual; Monday set date language ALTER SESSION set Nls_date_language='AMERICAN'; it can also be 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  from  Select1, 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'

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;
Note that I'm just lifting the nls_date_language, and of course there's a lot of
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 ')

Look for days between 2002-02-28 and 2002-02-01 except Monday and seven
Call Dbms_utility before and after each. Get_time, let's subtract the result (get 1/100 seconds instead of milliseconds).

9. Find 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

10. 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

One
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 the 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. Get the number of hours
Extract () Find the field value for the 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 (
) br> sql> Select Sysdate, To_char (sysdate, ' hh24 ') from dual;

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

13. Treatment of the month and day
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. 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

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.

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

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

19.5 seconds Clock 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

20. The day ordinal of a 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 is 1-7 or Sunday-Saturday, 1 means Sunday
Next_day (sysdate,6) is from the current beginning 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 Ddual
Date returned is natural after conversion to SS

24,round[rounded to the nearest date] (day: rounded to the nearest Sunday)
Select Sysdate S1,
Round (sysdate) S2,
Round (sysdate, ' Year ') of year,
Round (sysdate, ' Month ') month,
Round (sysdate, ' Day ') Day from dual

25,trunc[truncated to the nearest date, in days], the date type is returned
Select Sysdate S1,
Trunc (sysdate) S2,//return current date, no time seconds
Trunc (Sysdate, ' Year ') year,//return to the current January 1, minutes and seconds
Trunc (sysdate, ' Month ') month,//return to the 1st of the current month, no time or 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 the time difference
Note: The 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-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 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-seconds

28. Update Time
Note: Oracle time plus minus is in days, set to change the amount of N, so converted into years, days
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-seconds

29. 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_month
from dual;

From: http://hi.baidu.com/gzfvb/blog/item/5062b7f008eb70a8a50f5206.html

oracle-Date format Conversion