| 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(<date>, <number of months_integer> |
SELECT add_months(SYSDATE, 2) FROM dual;
-- but be aware of what it is doing
SELECT add_months(TO_DATE('27-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('28-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('29-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('30-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('31-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('01-FEB-2007'), 1) FROM dual; |
| |
| CURRENT_DATE |
Returns the current date in the session time zone, in 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;
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(<value> [,<return_format>[,<start_position>[,<length>]]])
| 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(<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; |
| |
| 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(<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; |
| |
| LENGTHB |
| Returns length in bytes | LENGTHB(<date>) |
| 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>) |
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;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; |
| |
| 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; |
| |
| Spelled Out Using TO_CHAR |
Spelled Demo |
|
SELECT TO_CHAR(TO_DATE('10:30:18', 'HH24:MI:SS'), 'HH24SP:MISP:SSSP')
FROM dual;
SELECT TO_CHAR(TO_DATE('01-JAN-2008', 'DD-MON-YYYY'), 'DDSP-MONTH-YYYYSP')
FROM dual;
SELECT TO_CHAR(TO_DATE('01-JAN-2008', '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 |
| SELECT SYSDATE FROM dual; |
| |
| TO_DATE |
In Oracle/PLSQL, the to_date function converts a string to a date. | TO_DATE(<string1>, [ format_mask ], [ nls_language ]) |
string1 is the string that will be converted to a date.The format_mask parameter is optional. It is the format that will be used to convert string1 to a date.
nls_language is optional. The nls_language parameter sets the
default language of the database. This language is used for messages,
day and month names, symbols for AD, BC, a.m., and p.m., and the default
sorting mechanism. This parameter also determines the default values of
the parameters NLS_DATE_LANGUAGE and NLS_SORT.
The following table shows options for the format_mask parameter. These parameters can be used in various combinations.
| Parameter | Explanation |
|---|
| YEAR | Year, spelled out alphabetically |
| YYYY | 4-digit year |
YYY
YY
Y | Last 3, 2, or 1 digit(s) of year. |
IYY
IY
I | Last 3, 2, or 1 digit(s) of ISO year. |
| IYYY | 4-digit year based on the ISO standard |
| RRRR | Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year. |
| Q | Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of the month. |
| MONTH | The name of month, padded with blanks to length of 9 characters. |
| RM | Roman numeral month (I-XII; JAN = I). |
| WW | The week of the year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
| W | The week of the month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
| IW | The week of year (1-52 or 1-53) based on the ISO standard. |
| D | Day of the week (1-7). Sunday is day 1 when
nls_territory is set to 'AMERICA' but differs if another nls_territory
is set (i.e. 'UNITED KINGDOM' or 'GERMANY' - in these cases Monday is 1. |
| DAY | Name of the day. |
| DD | The day of month (1-31). |
| DDD | The day of year (1-366). |
| DY | Abbreviated name of the day. (Mon, Tue, Wed, etc) |
| J | Julian day; the number of days since January 1, 4712 BC. |
| HH | Hour of day (1-12). |
| HH12 | Hour of day (1-12). |
| HH24 | Hour of day (0-23). |
| MI | Minute (0-59). |
| SS | Second (0-59). |
| SSSSS | Number of seconds past midnight (0-86399). |
| FF | Fractional seconds. Use a value from 1 to 9 after FF
to indicate the number of digits in the fractional seconds. For example,
'FF5'. |
| AM, A.M., PM, or P.M. | Meridian indicator |
| AD or A.D | AD indicator |
| BC or B.C. | BC indicator |
| TZD | Daylight savings identifier. For example, 'PST' |
| TZH | Time zone hour. |
| TZM | Time zone minute. |
| TZR | Time zone region. |
|
| 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; |
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 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 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; |
No comments:
Post a Comment