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
 
參考資料:Oracle Date Function
arrow
arrow
    全站熱搜

    jck11 發表在 痞客邦 留言(1) 人氣()