TO_DATE Format in Oracle

TO_DATE format (using time: 2007-11-02 13:45:25 as example)

Year: yy two digits — two-digit year — display: 07 yyy three digits — three-digit year — display: 007 yyyy four digits — four-digit year — display: 2007

Month: mm number — two-digit month — display: 11 mon abbreviated — character-set representation — display: 11月, or nov in the English version month spelled out — character-set representation — display: 11月, or november in the English version

Day: dd number — day of month — display: 02 ddd number — day of year — display: 02 dy abbreviated — day of week abbreviated — display: 星期五, or fri in the English version day spelled out — day of week full — display: 星期五, or friday in the English version ddspth spelled out, ordinal twelfth

Hour: hh two digits — 12-hour clock — display: 01 hh24 two digits — 24-hour clock — display: 13

Minute: mi two digits — 60-based — display: 45

Second: ss two digits — 60-based — display: 25

Others: Q digit — quarter — display: 4 WW digit — week of year — display: 44 W digit — week of month — display: 1

24-hour format range: 0:00:00 - 23:59:59… 12-hour format 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 to string select to_char(sysdate,“yyyy”) as nowYear from dual; // get the year select to_char(sysdate,“mm”) as nowMonth from dual; // get the month select to_char(sysdate,“dd”) as nowDay from dual; // get the day select to_char(sysdate,“hh24”) as nowHour from dual; // get the hour select to_char(sysdate,“mi”) as nowMinute from dual; // get the minute select to_char(sysdate,“ss”) as nowSecond from dual; // get the second

select to_date(“2004-05-07 13:23:44”,“yyyy-mm-dd hh24:mi:ss”) from dual//

select to_char( to_date(222,“J”),“Jsp”) from dual

Display: Two Hundred Twenty-Two

3. Find the day of the week select to_char(to_date(“2002-08-26”,“yyyy-mm-dd”),“day”) from dual; 星期一 select to_char(to_date(“2002-08-26”,“yyyy-mm-dd”),“day”,“NLS_DATE_LANGUAGE = American”) from dual; monday Set the date language ALTER SESSION SET NLS_DATE_LANGUAGE=“AMERICAN”; You can also do TO_DATE (“2002-08-26”, “YYYY-mm-dd”, “NLS_DATE_LANGUAGE = American”)

4. Days between two dates select floor(sysdate - to_date(“20020405”,“yyyymmdd”)) from dual;

5. Usage when time is null select id, active_date from table1 UNION select 1, TO_DATE(null) from dual; Note: use TO_DATE(null)

6. Month difference a_date between to_date(“20011201”,“yyyymmdd”) and to_date(“20011231”,“yyyymmdd”) Then 12:00 noon on Dec 31 and before 12:00 on Dec 1 are not included in this range. So when time needs precision, to_char is still necessary.

7. Date format conflict problem The input format depends on the ORACLE character set type you installed, e.g.: 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 it in to_date select to_char(to_date(“2002-08-26”,“yyyy-mm-dd”),“day”,“NLS_DATE_LANGUAGE = American”) from dual; Note I only gave NLS_DATE_LANGUAGE as an example; there are many others, you can view select * from nls_session_parameters select * from V$NLS_PARAMETERS

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 days between 2002-02-28 and 2002-02-01 excluding Monday and Sunday Call DBMS_UTILITY.GET_TIME before and after, then subtract the results (gives 1/100 second, not millisecond).

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. Next_day usage 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 row You can create a function to handle this 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 hours extract() finds the field value of a date or interval 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. Handling year, month, 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 )),12 ) MONTHS, newer_date, older_date from ( select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date from emp ) )

14. Handling variable days in a month select to_char(add_months(last_day(sysdate) +1, -2), “yyyymmdd”),last_day(sysdate) from dual

15. Find days in this year select add_months(trunc(sysdate,“year”), 12) - trunc(sysdate,“year”) from dual

Leap year handling to_char( last_day( to_date(“02” || :year,“mmyyyy”) ), “dd” ) If 28, not a leap year

17. Difference between yyyy and rrrr “YYYY99 TO_C

---

yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 2001

18. Handling different time zones select to_char( NEW_TIME( sysdate, “GMT”,“EST”), “dd/mm/yyyy hh:mi:ss”) ,sysdate from dual;

19. 5-second interval Select TO_DATE(FLOOR(TO_CHAR(sysdate,“SSSSS”)/300) * 300,“SSSSS”) ,TO_CHAR(sysdate,“SSSSS”) from dual 2002-11-1 9:55:00 35786 SSSSS means 5-digit seconds

20. Day of 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(A24) Hours, TRUNC(A2460 - 60TRUNC(A24)) Minutes, TRUNC(A246060 - 60TRUNC(A2460)) Seconds, TRUNC(A246060100 - 100TRUNC(A2460*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 years floor((date2-date1, 365) /30) as months d(mod(date2-date1, 365), 30) as days.

23. next_day function returns the date of next week, day is 1-7 or Sunday-Saturday, 1 means Sunday next_day(sysdate,6) is the next Friday from now. The number after counts from Sunday. 1 2 3 4 5 6 7 日 一 二 三 四 五 六

---

select (sysdate-to_date(“2003-12-03 12:55:45”,“yyyy-mm-dd hh24:mi:ss”))2460*60 from ddual Date returns days, then 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 [truncate to nearest date, unit is day], returns date type select sysdate S1, trunc(sysdate) S2, // returns current date, no time trunc(sysdate,“year”) YEAR, // returns Jan 1 of current year, no time trunc(sysdate,“month”) MONTH , // returns 1st of current month, no time trunc(sysdate,“day”) DAY // returns Sunday of current week, no time from dual

26. Return the latest date in a date list select greatest(“01-1月-04”,“04-1月-04”,“10-2月-04”) from dual

27. Calculate time difference Note: Oracle time difference is in days, so convert to years, months, days

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 diff - years select ceil(moths_between(sysdate-to_date(“2007-11-02 15:55:03”,“yyyy-mm-dd hh24:mi:ss”))) as spanMonths from dual // time diff - months select floor(to_number(sysdate-to_date(“2007-11-02 15:55:03”,“yyyy-mm-dd hh24:mi:ss”))) as spanDays from dual // time diff - days 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 diff - hours select floor(to_number(sysdate-to_date(“2007-11-02 15:55:03”,“yyyy-mm-dd hh24:mi:ss”))2460) as spanMinutes from dual // time diff - minutes select floor(to_number(sysdate-to_date(“2007-11-02 15:55:03”,“yyyy-mm-dd hh24:mi:ss”))246060) as spanSeconds from dual // time diff - seconds

28. Update time Note: Oracle time add/subtract is in days; let the change amount be n, so convert to years, months, 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 - years select to_char(sysdate,“yyyy-mm-dd hh24:mi:ss”),add_months(sysdate,n) as newTime from dual // change time - months 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 - days 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 - hours 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 first and last day of month 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;