Date and Time functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.
Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.
The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.
The datetime functions are:
- ADD_MONTHS
- CURRENT_DATE
- CURRENT_TIMESTAMP
- DBTIMEZONE
- EXTRACT (datetime)
- FROM_TZ
- LAST_DAY
- LOCALTIMESTAMP
- MONTHS_BETWEEN
- NEW_TIME
- NEXT_DAY
- ROUND (date)
- SESSIONTIMEZONE
- SYSDATE
- SYSTIMESTAMP
- TO_CHAR (datetime)
- TO_TIMESTAMP
- TO_TIMESTAMP_TZ
- TO_DSINTERVAL
- TO_YMINTERVAL
- TRUNC (date)
ADD_MONTHS
ADD_MONTHS returns the date date plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the datatype of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.
Examples
SELECT TO_CHAR(ADD_MONTHS(hire_date,1),'DD-MON-YYYY') "Next month"
FROM employees
WHERE last_name = 'Baer';
Next Month
-----------
07-JUL-2012
add_months('01-Aug-12', 3) would return '01-Nov-12'
add_months('01-Aug-12', -3) would return '01-May-12'
CURRENT_DATE
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
Examples
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_DATE --------------- -------------------- +04:00 15/04/2013 09:13:26
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.
Examples
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------- --------------------------------------------- +04:00 15/04/2013 09:20:22.333000 AM +04:00
DBTIMEZONE
DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.
Examples
SELECT DBTIMEZONE FROM DUAL; DBTIMEZONE ------ +00:00
EXTRACT (datetime)
EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.
Examples
SELECT EXTRACT(month FROM order_date) "Month",
COUNT(order_date) "No. of Orders"
FROM orders
GROUP BY EXTRACT(month FROM order_date)
ORDER BY "No. of Orders" DESC;
Month No. of Orders
---------- -------------
11 15
7 14
6 14
3 11
5 10
9 9
2 9
8 7
10 6
1 5
12 4
4 1
12 rows selected.
SELECT EXTRACT(YEAR FROM DATE '2013-04-10') FROM DUAL;
EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
2013
SELECT EXTRACT(MONTH FROM DATE '2013-04-10') FROM DUAL;
EXTRACT(MONTHFROMDATE'1998-03-07')
---------------------------------
04
SELECT EXTRACT(DAY FROM DATE '2013-04-10') FROM DUAL;
EXTRACT(DAYFROMDATE'1998-03-07')
--------------------------------
10
FROM_TZ
FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format.
Examples
SELECT FROM_TZ(TIMESTAMP '2013-03-28 08:00:00', '4:00') FROM DUAL; FROM_TZ(TIMESTAMP'2013-03-2808:00:00','4:00') ------------------------------------------------------- 28-MAR-13 08.00.00 AM +04:00
LAST_DAY
LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE.
Examples
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL; SYSDATE Last Days Left --------- --------- ---------- 30-MAR-13 31-MAR-13 1
LOCALTIMESTAMP
LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.
Examples
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL; CURRENT_TIMESTAMP LOCALTIMESTAMP ------------------------------------------------------------------- 15/04/2013 10:45:01.535000 AM +04:00 15/04/2013 10:45:01.535000 AM
MONTHS_BETWEEN
MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.
Examples
SELECT MONTHS_BETWEEN
(TO_DATE('03-03-2013','MM-DD-YYYY'),
TO_DATE('01-01-2013','MM-DD-YYYY') ) "Months"
FROM DUAL;
Months
----------
2.06451612
NEW_TIME
NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. The return type is always DATE, regardless of the datatype of date.
Examples
SELECT
new_time (to_date ('2013/02/15 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') "New Date and Time"
FROM DUAL
New Date and Time
--------------------
14/02/2013 10:45:00 AM
NEXT_DAY
NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE.
Examples
SELECT NEXT_DAY('02-FEB-2013','TUESDAY') "NEXT DAY" FROM DUAL;
NEXT DAY
-----------
05-FEB-2013
ROUND (date)
ROUND returns date rounded to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value.
Examples
SELECT ROUND (TO_DATE ('27-07-12','DD-MM-YY'),'YEAR') "New Year" FROM DUAL;
New Year
---------
01-01-13
SELECT ROUND (TO_DATE ('20-07-12','DD-MM-YY'),'MONTH') "New Month" FROM DUAL;
New Month
---------
01-08-12
ROUND Function Valid Format Parameters:
| Unit | Valid Format Parameters | Rounding Rule |
|---|---|---|
| Year | SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y | Rounds up on July 1st |
| Quarter | Q | Rounds up on the 16th day of the second month of the quarter |
| Month | MONTH, MON, MM, RM | Rounds up on the 16th day of the month |
| Week | WW | Same day of the week as the first day of the year |
| W | W | Same day of the week as the first day of the month |
| Day | DDD, DD, J | |
| Start day of the week | DAY, DY, D | |
| Hour | HH, HH12, HH24 | |
| Minute | MI |
SESSIONTIMEZONE
SESSIONTIMEZONE returns the time zone of the current session.
Examples
SELECT SESSIONTIMEZONE FROM DUAL; SESSION ------- +04:00
SYSDATE
SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE.
Examples
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL; NOW ------------------- 04-16-2013 12:31:27
SYSTIMESTAMP
SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.
Examples
SELECT SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP ----------------------------------------------- 16/04/2013 12:33:54.235000 PM +04:00 SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') "Fractional Seconds" FROM DUAL; Fractional Seconds --------------- 45345.500000
TO_CHAR (datetime)
TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt.
Examples
to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09' to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003' to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003' to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003' to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003' to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'
List of Valid Parameters For the TO_CHAR (datetime) Function
| Parameter | Explanation |
|---|---|
| YEAR | Year, spelled out |
| 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 |
| Q | Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of month. |
| MONTH | Name of month, padded with blanks to length of 9 characters. |
| RM | Roman numeral month (I-XII; JAN = I). |
| WW | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
| W | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
| IW | Week of year (1-52 or 1-53) based on the ISO standard. |
| D | Day of week (1-7). |
| DAY | Name of day. |
| DD | Day of month (1-31). |
| DDD | Day of year (1-366). |
| DY | Abbreviated name of day. |
| 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 | Seconds past midnight (0-86399). |
| FF | Fractional seconds. |
TO_TIMESTAMP
TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.
Examples
SELECT TO_TIMESTAMP ('10-09-2012 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF')
FROM DUAL;
TO_TIMESTAMP('10-09-0214:10:10.123000','DD-MM-RRHH24:MI:SS.FF')
-------------------------------------------------------------------
10/09/2012 02:10:10.123000000 PM
TO_TIMESTAMP_TZ
TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.
SELECT TO_TIMESTAMP_TZ('2012-12-01 11:00:00 +4:00',
'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
TO_TIMESTAMP_TZ('2012-12-0111:00:00+04:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------
01/12/2012 11:00:00.000000000 AM +04:00
TO_DSINTERVAL
TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value.
Examples
SELECT employee_id, last_name FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00')
<= DATE '2012-01-01';
EMPLOYEE_ID LAST_NAME
----------- ---------------
100 King
101 Kochhar
200 Whalen
TO_YMINTERVAL
TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted.
Examples
SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
FROM employees;
HIRE_DATE 14 months
--------- ---------
11/01/2013 11/03/2014
03/01/2013 03/03/2014
10/01/2013 10/03/2014
11/01/2013 11/03/2014
20/12/2012 20/02/2014
20/12/2012 20/02/2014
02/01/2013 02/03/2014
TRUNC (date)
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE.
Examples
SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'YEAR') "Trunc Year" FROM DUAL;
Trunc Year
---------
01/01/2012
SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'MONTH') "Trunc Month" FROM DUAL;
Trunc Month
---------
01/10/2012
For Trunc (date) Function Valid Format Parameters
| Unit | Valid format parameters |
|---|---|
| Year | SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y |
| ISO Year | IYYY, IY, I |
| Quarter | Q |
| Month | MONTH, MON, MM, RM |
| Week | WW |
| IW | IW |
| W | W |
| Day | DDD, DD, J |
| Start day of the week | DAY, DY, D |
| Hour | HH, HH12, HH24 |
| Minute | MI |
No comments:
Post a Comment