| 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 |
|
|
|
| |
| + 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(left IN DATE, right IN NUMBER) RETURN DATE; |
SELECT add_months(SYSDATE, 2) FROM dual;
-- but be aware of what it is doing
SELECT add_months(TO_DATE('27-JAN-2010'), 1) FROM dual;
SELECT add_months(TO_DATE('28-JAN-2010'), 1) FROM dual;
SELECT add_months(TO_DATE('29-JAN-2010'), 1) FROM
dual;
SELECT add_months(TO_DATE('30-JAN-2010'), 1) FROM dual;
SELECT add_months(TO_DATE('31-JAN-2010'), 1) FROM dual;
SELECT add_months(TO_DATE('01-FEB-2010'), 1) FROM dual; |
| |
| CURRENT_DATE |
| Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE |
CURRENT_DATE RETURN 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;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; |
| |
| DUMP |
| Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value |
DUMP(
e IN DATE, -- expression
df IN PLS_INTEGER := NULL, -- return format
sp IN PLS_INTEGER := NULL, -- starting position
len IN PLS_INTEGER := NULL) -- length
RETURN VARCHAR2;
| 8 |
Octal |
| 10 |
Decimal |
| 16 |
Hexidecimal |
| 17 |
Single Characters |
| 1008 |
Octal notation with the character set name |
| 1010 |
Decimal notation with the character set name |
| 1016 |
Hexadecimal notation with the character set name |
| 1017 |
Single characters with the character set name |
|
col drows format a40
SELECT DUMP(SYSDATE) DROWS FROM dual;
SELECT DUMP(SYSDATE, 8) DROWS FROM dual;
SELECT DUMP(SYSDATE, 16) DROWS FROM dual; |
| |
| GREATEST |
| Return the Latest Date |
GREATEST(pattern IN DATE) RETURN 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; |
| |
| INTERVAL |
| Interval to adjust date-time |
INTERVAL '<integer>' <unit> |
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM dual;
SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;
SELECT TO_CHAR(SYSDATE - INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual; |
| |
| LAST_DAY |
| Returns The Last Date Of A Month |
LAST_DAY(right IN DATE) RETURN DATE; |
SELECT * FROM t;
SELECT LAST_DAY(datecol1) FROM t; |
| |
| LEAST |
| Return the Earliest Date |
LEAST(pattern IN DATE) RETURN DATE; |
SELECT * FROM t;
SELECT LEAST(datecol1, datecol2, datecol3) FROM t; |
| |
| LENGTH |
| Returns length in characters |
LENGTH(<date>) RETURN INTEGER; |
| SELECT LENGTH(last_ddl_time) FROM user_objects; |
| |
| LENGTHB |
| Returns length in bytes |
LENGTHB(<date>) RETURN INTEGER; |
| SELECT LENGTHB(last_ddl_time) FROM user_objects; |
| Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available. |
| |
| MAX |
| Return the Latest Date |
MAX(<date>) RETURN INTEGER; |
SELECT * FROM t;
SELECT MAX(datecol1) FROM t; |
| |
| MIN |
| Return the Earliest Date |
MIN(<date>) RETURN INTEGER; |
SELECT * FROM t;
SELECT MIN(datecol1) FROM t; |
| |
| MONTHS_BETWEEN |
| Returns The Months Separating Two Dates |
MONTHS_BETWEEN(left IN DATE, right IN DATE) RETURN NUMBER; |
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. |
NEW_TIME(right IN DATE, middle IN VARCHAR2, left IN VARCHAR2) RETURN DATE; |
SELECT NEW_TIME(TO_DATE('11-10-2010 01:23:45',
'MM-DD-YYYY 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-2010 01:23:45',
'MM-DD-YYYY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; |
| |
| NEXT_DAY |
| Date of next specified date following a date |
NEXT_DAY(left IN DATE, right IN VARCHAR2) RETURN DATE;
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
Overload 1 |
ROUND(right IN DATE) RETURN DATE; |
SELECT ROUND(TO_DATE('10-OCT-10 10:10:10', 'DD-MON-RR HH24:MI:SS')) NEW_YEAR
FROM dual; |
Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day
Overload 2 |
(<date_value>, <format>)
ROUND(left IN DATE, right IN VARCHAR2) RETURN DATE; |
SELECT ROUND(TO_DATE('27-OCT-2010'),'YEAR') NEW_YEAR
FROM dual; |
| |
| Spelled Out Using TO_CHAR |
| Spelled Demo |
| DDSP |
HH24SP |
MISP |
MMSP |
SSSP |
|
SELECT TO_CHAR(TO_DATE('10:30:18', 'HH24:MI:SS'), 'HH24SP:MISP:SSSP')
FROM dual;
SELECT TO_CHAR(TO_DATE('01-JAN-2011', 'DD-MON-YYYY'), 'DDSP-MONTH-YYYYSP')
FROM dual;
SELECT TO_CHAR(TO_DATE('01-JAN-2011', 'DD-MM-YYYY'), 'DDSP-MMSP-YYYYSP')
FROM dual;
SELECT TO_CHAR(TO_DATE(sal,'J'), 'JSP')
FROM emp; |
| |
| SYSDATE |
| Returns the current date and time set for the operating system on which the database resides |
SYSDATE RETURN DATE; |
| SELECT SYSDATE FROM dual; |
| |
| TRUNC |
| Convert a date to the date at midnight |
TRUNC(left IN DATE) RETURN DATE; |
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(left IN DATE, right IN VARCHAR2) RETURN DATE; |
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; |
| Dates in WHERE Clause Joins |
SELECT SYSDATE FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SYSDATE FROM dual;
/
/
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
CREATE TABLE t (
datecol DATE);
INSERT INTO t
(datecol)
VALUES
(SYSDATE);
SELECT * FROM t;
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT * FROM t;
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
SELECT * FROM t;
SELECT SYSDATE FROM dual;
SELECT * FROM t
WHERE datecol = SYSDATE;
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT * FROM t;
SELECT SYSDATE FROM dual;
SELECT TRUNC(SYSDATE) FROM dual;
SELECT * FROM t
WHERE TRUNC(datecol) = TRUNC(SYSDATE);
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; |
| |
| 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;
/ |
| Business Date function, above, enhanced by Larry Benton to handle negative values for the days2add parameter. |
CREATE OR REPLACE FUNCTION business_date (start_date DATE, days2add NUMBER) RETURN DATE DETERMINISTIC AUTHID DEFINER IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
Direction INTEGER := 1; -- days after start_date
BusinessDays NUMBER := Days2Add;
BEGIN
IF Days2Add < 0 THEN
Direction := - 1; -- days before start_date
BusinessDays := (-1) * BusinessDays;
END IF;
WHILE Counter < BusinessDays LOOP
CurDate := CurDate + Direction;
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 + (Direction * (Counter + SkipCntr));
END business_date;
/ |
| Returns The First Day Of A Month |
CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE) RETURN DATE
DETERMINISTIC AUTHID DEFINER 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; |
Obtain counts per ten minute increment
Posted by Michele Cadot
03/09/08 to c.d.o.misc |
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT
TRUNC(SYSDATE) + dbms_random.value(0,86400)/86400
FROM dual
CONNECT BY LEVEL <= 10;
WITH data AS (
SELECT TRUNC(SYSDATE)+dbms_random.value(0,86400)/86400 h
FROM dual
CONNECT BY LEVEL <= 10)
SELECT TO_CHAR(h,'DD/MM/YYYY HH24:MI:SS') h, TO_CHAR(TRUNC(h) +
TRUNC(TO_CHAR(h,'SSSSS')/600)/144, 'DD/MM/YYYY HH24:MI:SS') "10m"
FROM data
ORDER BY h; |
