Oracle Date Functions
来源:互联网 发布:js教程视频下载 编辑:程序博客网 时间:2024/05/27 01:47
Oracle Date FunctionsVersion 11.1 Would you like to take a class from Ace Director Dan Morgan the author of this library?
"Database Essentials" class begin when you register at SQL University.
Try the free Sample Courses and then check out the list of classes and topics.zz
SYSDATESELECT 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
Add A Month To A DateADD_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 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;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DUMPReturns The Number Of Bytes And Datatype Of A ValueDUMP(<value>)SELECT DUMP(SYSDATE) FROM dual; GREATEST
Return the Latest DateLEAST(<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-timeINTERVAL '<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_DAYReturns The Last Date Of A MonthLAST_DAY(<date>)SELECT * FROM t;
SELECT LAST_DAY(datecol1) FROM t; LEASTReturn the Earliest DateLEAST(<date>, <date>, <date>, ...)SELECT * FROM t;
SELECT LEAST(datecol1, datecol2, datecol3) FROM t; LENGTHReturns length in charactersLENGTH(<date>)SELECT LENGTH(last_ddl_time) FROM user_objects; LENGTHBReturns length in bytesLENGTHB(<date>)SELECT LENGTHB(last_ddl_time) FROM user_objects;Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available. MAXReturn the Latest DateMAX(<date>)SELECT * FROM t;
SELECT MAX(datecol1) FROM t; MINReturn the Earliest DateMIN(<date>)SELECT * FROM t;
SELECT MIN(datecol1) FROM t; MONTHS_BETWEENReturns The Months Separating Two DatesMONTHS_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 dateBefore 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_DAYDate of next specified date following a dateNEXT_DAY(<date>, <day of the week>)
Options are SUN, MON, TUE, WED, THU, FRI, and SATSELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual; ROUNDReturns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest dayROUND(<date_value>, <format>)SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR
FROM dual; Spelled Out Using TO_CHAR
Spelled Demo
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; SYSDATEReturns the current session DateTimeSYSDATESELECT SYSDATE FROM dual; TRUNC
Convert a date to the date at midnightTRUNC(<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 JoinsSELECT 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'; VSIZEReturns The Number Of Bytes Required By A ValueVSIZE(e IN DATE) RETURN NUMBERSELECT VSIZE(SYSDATE) FROM dual; Date Calculations
Returns A Day A Specified Number Of Days In The Future Skipping WeekendsCREATE 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 MonthCREATE 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 ValuesCREATE 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.miscSELECT 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.miscALTER 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; http://blog.csdn.net/tingqier/archive/2008/10/08/3033884.aspxRelated TopicsCastConversion FunctionsMiscellaneous FunctionsNumeric FunctionsString FunctionsTimestamp
"Database Essentials" class begin when you register at SQL University.
Try the free Sample Courses and then check out the list of classes and topics.zz
Have you seen our new Functions page? If not ... Click Here ... for instant access to all Oracle functions
DateCurrent DateCURRENT_DATESYSDATESELECT 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
DayMonthYearFill ModeJulian DateDMMYYFMJDDMONYYYY DDTH RR DAY RRRR
+ AND -+<date> + <integer>SELECT SYSDATE + 1 FROM dual;-<date> - <integer>SELECT SYSDATE - 1 FROM dual; ADD_MONTHSAdd A Month To A DateADD_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 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;
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DUMPReturns The Number Of Bytes And Datatype Of A ValueDUMP(<value>)SELECT DUMP(SYSDATE) FROM dual; GREATEST
Return the Latest DateLEAST(<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-timeINTERVAL '<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_DAYReturns The Last Date Of A MonthLAST_DAY(<date>)SELECT * FROM t;
SELECT LAST_DAY(datecol1) FROM t; LEASTReturn the Earliest DateLEAST(<date>, <date>, <date>, ...)SELECT * FROM t;
SELECT LEAST(datecol1, datecol2, datecol3) FROM t; LENGTHReturns length in charactersLENGTH(<date>)SELECT LENGTH(last_ddl_time) FROM user_objects; LENGTHBReturns length in bytesLENGTHB(<date>)SELECT LENGTHB(last_ddl_time) FROM user_objects;Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available. MAXReturn the Latest DateMAX(<date>)SELECT * FROM t;
SELECT MAX(datecol1) FROM t; MINReturn the Earliest DateMIN(<date>)SELECT * FROM t;
SELECT MIN(datecol1) FROM t; MONTHS_BETWEENReturns The Months Separating Two DatesMONTHS_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 dateBefore 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_DAYDate of next specified date following a dateNEXT_DAY(<date>, <day of the week>)
Options are SUN, MON, TUE, WED, THU, FRI, and SATSELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual; ROUNDReturns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest dayROUND(<date_value>, <format>)SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR
FROM dual; Spelled Out Using TO_CHAR
Spelled Demo
DDSPHH24SPMISPMMSPSSSP
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; SYSDATEReturns the current session DateTimeSYSDATESELECT SYSDATE FROM dual; TRUNC
Convert a date to the date at midnightTRUNC(<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 JoinsSELECT 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'; VSIZEReturns The Number Of Bytes Required By A ValueVSIZE(e IN DATE) RETURN NUMBERSELECT VSIZE(SYSDATE) FROM dual; Date Calculations
Returns A Day A Specified Number Of Days In The Future Skipping WeekendsCREATE 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 MonthCREATE 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 ValuesCREATE 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.miscSELECT 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.miscALTER 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; http://blog.csdn.net/tingqier/archive/2008/10/08/3033884.aspxRelated TopicsCastConversion FunctionsMiscellaneous FunctionsNumeric FunctionsString FunctionsTimestamp
- Oracle Date Functions
- Oracle Date Functions
- Character Functions & Number Functions & Date Functions
- Oracle Functions
- T-SQL Classic Date Functions
- mysql 5.6 Date/Time Functions
- Oracle Pipelined Table Functions
- Oracle/PLSQL: Creating Functions
- oracle analytic functions
- Oracle Number Functions
- Oracle Character Functions
- Oracle Pipelined Table Functions
- Oracle SQL string functions
- Oracle Pipelined Table Functions
- oracle single row functions
- Some functions and codes about date
- Date Functions – SQL Server vs MySQL
- mysql 12.7 Date and Time Functions
- 斯坦福大学开放课程——编程方法 作业5
- 适合小型winform软件的权限思路
- 深入理解Android消息处理系统——Looper、Handler、Thread
- 适合小型winform软件的权限思路
- VMWare下安装MAC OS X Snow Leopard 10.6
- Oracle Date Functions
- TextView 或者 EidtView 。。。。 显示文字如果超长, 显示 ...
- 又一个Wordpress主题在线制作网站-超强推荐
- Windows Phone 7 系统信息获取示例
- 判断DataTable某列数据是否唯一
- 关闭窗口时提示是否关闭
- Amazon EC2 取得自己的外部IP和域名
- JNI 数据类型转换
- 远程安装Linux操作系统