Oracle time date operation (original)

xiaoxiao2021-03-05 27

Sysdate (5/24/60/60) Delayed 5 seconds on the system time Sysdate 5/24/60 Delayed 5 minutes on system time SYSDATE 5/24 Delayed 5 hours on the system time Sysdate 5 On the basis of system time, delay 5 days add_months (sysdate, -5) on the system time, delay in May ADD_MONTHS (Sysdate, -5 * 12) on the system time, 5 years

Date of last month: SELECT LAST_DAY (add_months (sysdate, -1)) from dual; SELECT TRUNC (Add_MontHS (Sysdate, 1), 'mm') - 1/24/60/60 from Dual Date of Monday this week: SELECT TRUNC (Sysdate, 'Day') 1 from Dual

Number of days from the beginning of the year: SELECT CEIL (Sysdate - Trunc (sysdate, 'year')) from Dual;

Today is the first few weeks this year: select to_char (sysdate, 'fmww') from Dual is the first few weeks of this month: select to_char (sysdate, 'ww') - to_char (trunc (tysdate, 'mm'), ' WW ') 1 AS "Weekofmon" from DUAL

This month's days Select TO_CHAR (Last_Day (sysdate), 'DD') Days from Dual This year SELECT Add_Months (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from Dual Next Week One date SELECT next_day (sysdate, 'monday') from Dual

==============================================

- Calculate working day

Create Table T (s Date, E Date); ALTER Session Set NLS_DATE_FORMAT = 'YYYY-mm-Dd'; Insert Into T Values ('2003-03-01', '2003-03-03'); Insert Into T Values ('2003-03-02'); INSERT INTO T VALUES ('2003-03-07', '2003-03-08'); Insert Into T Values ('2003-03- 07 ',' 2003-03-09 '); INSERT INTO T Values (' 2003-03-05 ',' 2003-03-07 '); Insert Into T Values (' 2003-02-01 ',' 2003- 03-31 '); - here assume that the date is without time, otherwise it can be added before all the date. Select S, E, E-S 1 Total_Days, Trunc ((E-S 1) / 7) * 5 Length (Replace (Substr ('0111100111110', TO_CHAR (S, 'D'), MOD (E- S 1, 7)), '0', '')) Work_Days from T;

- DROP TABLE T;

Lead: http://www.itpub.net/showthread.php? S = 1635506cd5f48b1bc3adbe4cde96f227 & threadid = 104060 & perpage = 15 & PageNumber = 1

============================================================================================================================================================================================================= ===============================

Judging the current time is morning in the afternoon or at night

SELECT CASE WHEN TO_NUMBER (to_char (sysdate, 'hh24)) BetWeen 6 and 11 Ten' morning 'when to_number (to_char (sysdate,' hh24)) BetWeen 11 and 17 Ten 'afternoon' when to_number (to_char (sysdate, ' HH24 ')) BETWEEN 17 and 21 TEN' Evening 'endfrom dual;

Convert digital to any time format. Such as seconds: need to convert to days / hour Select to_char (floor (Trunc (Trunc (Trunc (Trunc (Trunc (Trunc (936000 "|| To_char (Trunc) / (60 * 60)), 24)) || 'h' FROM DUALTO_DATE format Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out march Year : Yy two digits 98 YYYY FOUR DIGITS 1998 24 hours format Under time range: 0:00:00 - 23:59:59 .... 12 hours format Under time range: 1:00:00 - 12:59: 59 .... 1. Date and character conversion function usage (to_date, to_char) 2. Select to_char (to_date (222, 'j'), 'JSP') from Dual Display Two Hundred TWENTY-TWO 3. Ask for a day Several 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 Settings Date Language ALTER Session Set NLS_DATE_LANGUAGE =' American '; can also be to_date (' 2002-08 -26 ',' YYYY-MM-DD ',' NLS_DATE_LANGUAGE = American ') 4. The number of days during the two days Select floor (sysdate - to_date (' 20020405 ',' YYYMMDD ')) from Dual; 5. Time is NULL usage select id, active_date from table1 UNION select 1, TO_DATE (null) from dual; note that use TO_DATE (null) 6. a_date between to_date ( '20011201', 'yyyymmdd') and to_date ( '20011231', 'yyyymmdd' Then, after 12 o'clock on December 31, it is not included in this range before 12 o'clock on December 1.

So, when time needs to be exactly, try to_char is still necessary 7. Date format conflict issues Enter the format to see the type of Oracle character set you installed, such as US7ASCII, Date format is: '01 -jan-01 'alter system set NLS_DATE_LANGUAGE = American alter session set NLS_DATE_LANGUAGE = American to_date or write in the select to_char (to_date (' 2002-08-26 ',' yyyy-mm-dd '),' day ',' NLS_DATE_LANGUAGE = American ') from dual; pay attention to me this is just a NLS_DATE_LANGUAGE, of course, there are many, you can see 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 2002-02-01 Said Monday and Seven days DBMS_UTILITY.GET_TIME is called before and after, it will subtilize the result (get 1/100 seconds, not milliseconds). 9. 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, FORMAT CODE DAY MON-Sun, For Format Code DY 1-7, 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, the same function can create a function to handle this problem Create or Replace function sys_date return date is beginning, qRN sysdate; end; select to_char (sys_date, 'hh: mi: ss') from all_Objects; 12. Get a small number of SELECT EXTRACT (HOUR from timestamp '2001-02-16 2: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 Get the Year of the Year and this similar 13.-year-month SELECT OLDER_DATE, NEWER_DATE , Years, Months, ABS (Trunc (NEWER_DATE- Add_MONTHS (Older_Date, Years * 12 Months))))))))) "" SELECT TRUNC (NEWER_DATEEEN (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. Processing February days indefinite way select to_char (add_months (last_day (sysdate ) 1, -2), 'YYYYMMDD'), Last_Day (sysdate) from Dual 16. Find this year SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from Dual leap year treatment method to_char (to_day (to_date ('02 ')),' dd ') If it is 28, it is not a leap year 17. The difference between 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 seconds a spacing 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 represents 5-digit seconds 20. Select to_char (sysdate, 'ddd'), SESDATE AROUNG 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, 'YYYYMMDDH24MISS'); // Floor ((DATE2-date1) / 365) As the year Floor (Date2-Date1, 365) / 30) as the month MOD (MOD (Date2-Date1, 365), 30) as the day. 23.Next_day function next_day ( Sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday. 1 2 3 4 5 6 7 Days, two three four five six ------------------------------------ --------------------------- Select (sysdate-to_date ('2003-12-03 12:55:45 ",' YYYY-mm -DD HH24: MI: SS ') * 24 * 60 * 60 From Dual Date Returns the day and then converted to SS turn this: http://www.onlineDatabase.cn/leadbbs/announce/announce.asp? BoardID = 42 & id = 1769

转载请注明原文地址:https://www.9cbs.com/read-35087.html

9cbs

New Post(0)