Oracle Date Processing Complete Edition
Date processing full version
TO_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
The time range for 24 hours format: 0:00:00 - 23:59:59 ....
12 hours of formatted 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
Show Two Hundred TWENTY-TWO
3. Seeking a day is the day
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')
4.
Number of days during the two days
Select floor (sysdate)
TO_DATE ('20020405', 'YYYYMMDD'))) from Dual;
5. Time for NULL
SELECT ID, ACTIVE_DATE from Table1
Union
SELECT 1,
TO_DATE (NULL) from DUAL
Note
TO_DATE (NULL)
6. a_date between
TO_DATE ('20011201', 'YYYYMMDD') AND
TO_DATE ('2001231', 'YYYYMMDD')
Then, 12:00 on December 31, it is not included in this range before 12 o'clock on December 1st.
So, when time needs to be precise, try to_char is still necessary.
7. Date format conflict problem
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SYSTEM SET NLS_DATE_LANGUAGE = American
ALTER session set nls_date_language = american
Or in
TO_DATE
SELECT TO_CHAR
TO_DATE ('2002-08-26', 'YYYY-MM-DD'), 'Day', 'NLS_DATE_LANGUAGUAGE = American') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
SELECT * NLS_SESSION_PARAMETERSSELECT * 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
The DBMS_UTILITY.GET_TIME is called before and after, and the result is subtracted (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. 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 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.
Number of hours
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
Get the Year of the Year and this similar
13.
Annual 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. Processing the number of months of the month
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day)
TO_DATE ('02 '||: Year,' MMYYYY ')),' 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', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19. 5 seconds an interval
SELECT
To_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20. The first few days of the year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, 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)
Floor ((Date2-Date1, 365) / 30) as a 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
Day and two three four five six
Posted on July 21, 2004 10:20 am
