Date |
Current Date |
CURRENT_DATE
SYSDATE |
SELECT TO_CHAR(CURRENT_DATE,
'DD-MON-YYYY HH:MI:SS') FROM dual;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual; |
Formats |
Day |
Month |
Year |
Fill Mode |
Julian Date |
D |
MM |
YY |
FM |
J |
DD |
MON |
YYYY |
|
|
DDTH |
|
RR |
|
|
DAY |
|
RRRR |
|
|
|
Formats That Can Be Used
With ROUND And TRUNC Functions |
- One greater than the first two digits of a four-digit year
CC
SCC
- Year (rounds up on July 1)
SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
- ISO Year
IYYY
IY
IY
I
- Quarter (rounds up on 16th day of the 2nd month of the quarter)
Q
- Month (rounds up on the sixteenth day)
MONTH
MON
MM
RM
- Week of the year
WW
- Same day of the week as the first day of the ISO year
IW
- Same day of the week as the first day of the month
W
- Day
DDD
DD
J
- Starting day of the week
DAY
DY
D
- Hours
HH
HH12
HH24
- Minute
MI |
|
+ AND - |
+ |
<date> + <integer> |
SELECT SYSDATE + 1 FROM dual; |
- |
<date> - <integer> |
SELECT SYSDATE -
1 FROM dual; |
|
ADD_MONTHS |
Add A Month To A Date |
ADD_MONTHS(<date>, <number of
months_integer> |
SELECT add_months(SYSDATE, 2) FROM
dual; |
|
CURRENT_DATE |
Returns the current date of the server
as a value in the Gregorian calendar of datatype DATE |
|
col sessiontimezone format a30
SELECT sessiontimezone, current_date
FROM dual;
ALTER SESSION SET TIME_ZONE =
'-5:0';
SELECT sessiontimezone, current_date
FROM dual;
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT sessiontimezone, current_date
FROM dual;
ALTER SESSION SET TIME_ZONE = '-7:0';
SELECT sessiontimezone, current_date
FROM dual; |
|
DUMP |
Returns The Number Of Bytes And Datatype Of
A Value |
DUMP(<value>) |
SELECT DUMP(SYSDATE) FROM dual; |
|
GREATEST |
Return the Latest Date |
LEAST(<date>, <date>, <date>, ...) |
CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0;
INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;
SELECT * FROM t;
SELECT GREATEST(datecol1, datecol2, datecol3)
FROM t; |
|
LAST_DAY |
Returns The Last Date Of A Month |
LAST_DAY(<date>) |
SELECT * FROM t;
SELECT LAST_DAY(datecol1) FROM t; |
|
LEAST |
Return the Earliest Date |
LEAST(<date>, <date>, <date>, ...) |
SELECT * FROM t;
SELECT LEAST(datecol1, datecol2, datecol3)
FROM t; |
|
LENGTH |
Returns length in
characters |
LENGTH(<date>) |
SELECT
LENGTH(last_ddl_time) FROM user_objects; |
Note: Additional forms of
LENGTH (LENGTHB, LENGTHC, LENGTH2, and LENGTH4) are also available. |
|
LENGTHB |
Returns length in bytes |
LENGTHB(<date>) |
SELECT
LENGTHB(last_ddl_time) FROM user_objects; |
|
MAX |
Return the Latest Date |
MAX(<date>) |
SELECT * FROM t;
SELECT MAX(datecol1) FROM t; |
|
MIN |
Return the Earliest Date |
MIN(<date>) |
SELECT * FROM t;
SELECT MIN(datecol1) FROM t; |
|
MONTHS_BETWEEN |
Returns The Months Separating Two Dates |
MONTHS_BETWEEN(<latest_date>,
<earliest_date>) |
SELECT MONTHS_BETWEEN(SYSDATE+365,
SYSDATE-365) FROM dual;
SELECT MONTHS_BETWEEN(SYSDATE-365, SYSDATE+365) FROM dual; |
|
NEW_TIME |
Returns the date and time in time zone
zone2 when date and time in time zone zone1 are date |
Before using this function, you must
set the NLS_DATE_FORMAT parameter to display 24-hour time. |
SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT =
'DD-MON-YYYY HH24:MI:SS';
SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'),
'AST', 'PST') "New Date and Time"
FROM dual; |
|
NEXT_DAY |
Date of next specified date following a date |
NEXT_DAY(<date>, <day of the week>)
Options are SUN, MON, TUE, WED, THU, FRI, and SAT |
SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual; |
|
ROUND |
Returns date rounded to the unit specified
by the format model. If you omit the format, the date is rounded to the nearest day |
ROUND(<date_value>, <format>) |
SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR
FROM dual; |
|
SYSDATE |
Returns the current session DateTime |
SYSDATE |
SELECT SYSDATE FROM dual; |
|
TRUNC |
Convert a date to the date at midnight |
TRUNC(<date_time>) |
CREATE TABLE t (
datecol DATE);
INSERT INTO t (datecol) VALUES (SYSDATE);
INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE));
INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'HH'));
INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'MI'));
COMMIT;
SELECT TO_CHAR(datecol, 'DD-MON-YYYY HH:MI:SS')
FROM t; |
Selectively remove part of the date information
Special thanks to Dave Hayes for reminding me of this. |
TRUNC(<date_time>, '<format>') |
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
FROM dual;
-- first day of the month
SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;
SELECT TO_CHAR(TRUNC(SYSDATE, 'MON'),
'DD-MON-YYYY HH:MI:SS')
FROM dual;
SELECT TO_CHAR(TRUNC(SYSDATE, 'MONTH'),
'DD-MON-YYYY HH:MI:SS')
FROM dual;
-- first day of the year
SELECT TO_CHAR(TRUNC(SYSDATE, 'YYYY'),
'DD-MON-YYYY HH:MI:SS')
FROM dual;
SELECT TO_CHAR(TRUNC(SYSDATE, 'YEAR'),
'DD-MON-YYYY HH:MI:SS')
FROM dual; |
|
VSIZE |
Returns The Number Of Bytes Required By A Value |
VSIZE(e IN DATE) RETURN NUMBER |
SELECT VSIZE(SYSDATE) FROM dual; |
|
Date Calculations |
Returns A Day A Specified Number Of Days In The Future Skipping Weekends |
CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
BEGIN
WHILE Counter < Days2Add
LOOP
CurDate := CurDate+1;
DayNum := TO_CHAR(CurDate, 'D');
IF DayNum BETWEEN 2 AND 6 THEN
Counter := Counter + 1;
ELSE
SkipCntr := SkipCntr + 1;
END IF;
END LOOP;
RETURN start_date + Counter + SkipCntr;
END business_date;
/
|
Returns The First Day Of A Month |
CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/ |
|
Time Calculations |
Returns The Number Of Seconds Between Two Date-Time Values |
CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS
NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);
BEGIN
-- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));
-- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));
-- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));
-- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));
RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/ |
Calculating time from seconds
Posted by John K. Hinsdale
12/30/06 to c.d.o.misc |
SELECT DECODE(FLOOR(999999/86400), 0, '',
FLOOR(999999/86400) || ' day(s), ') ||
TO_CHAR(TO_DATE(MOD(999999, 86400),'SSSSS'), 'HH24:MI:SS') AS elapsed
FROM dual; |
Calculate trimester |
SELECT FLOOR((&month-1)/4)
+ 1
FROM dual;
-- enter values from 1 to 12 |
留言列表