Oracle 11g Release 1 (11.1) Single line function--date function

Date function
Date functions manipulate Date values (fields of date type), timestamp values (TIMESTAMP, TIMESTAMP with time ZONE, and TIMESTAMP with the fields of the local time ZONE type), and interval values (INTERVAL day to SECOND and INTERVAL year to MONTH).

Most date functions, such as add_months, Current_date, Last_day, New_time, and Next_day, are designed for Oracle date types. If you provide a timestamp value as their parameter, the Oracle database internally converts the input type to a DATE value and returns. When a number value is returned, the Months_between function throws an exception, and the ROUND and TRUNC functions do not accept timestamps or interval values.

The remaining date functions are designed to accept three date types, such as date, timestamp, and interval, and return a value for those types.

All date functions that return the system date information, such as Sysdate, Systimestamp, Current_timestamp, and so on, are recalculated for each SQL statement, no matter how many times the statements are referenced.

Add_months

Current_date

Current_timestamp

Dbtimezone

EXTRACT (DateTime)

From_tz

Last_day

Localtimestamp

Months_between

New_time

Next_day

Numtodsinterval

Numtoyminterval

ROUND (date)

Sessiontimezone

Sys_extract_utc

Sysdate

Systimestamp

To_char (DateTime)

To_timestamp

To_timestamp_tz

To_dsinterval

To_yminterval

TRUNC (date)

Tz_offset

Add_months (Date,integer)

--------------------------------------------------------------------------------

Add_months returns the date of the date plus the integer of the month. The month is defined by the session parameter Nls_calendar.

The date parameter can be a day value, or any value that can be implicitly converted to a date type. 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 type of the parameter date.

If date is the last day of the month, or the number of days in the result month is less than date, the day of the result is the last day of the result month. Otherwise, the same day as date.

Example 1: Demo Employees table hire_date field plus one months.

Sql> SELECT to_char (hire_date, ' yyyy-mm-dd ') "Current Month", 2 To_char (Add_months (hire_date, 1), ' YYYY-MM-DD ' "Next Month", 3 To_char (Add_months (to_date (' 2012-2-29 ', ' Yyyy-mm-dd '), 1), 4 ' yyyy-mm-dd ') "Fe Wer days "5 from Employees 6 WHERE last_name = ' Baer '; Current month Next month fewer---------------------------------1994-06-07 1994-07-07 2012-03-31 sql>

Current_date

--------------------------------------------------------------------------------

Current_date returns the current date of the session time zone, with the value of the date type Gregorian calendar (Gregorian) Day value.

Example 2: Demo current_date.

Sql> sql> ALTER session SET time_zone = ' -5:0 '; Session alteredsql> ALTER Session SET Nls_date_format = ' yyyy-mm-dd HH24:MI:SS '; Session alteredsql> SELECT Sessiontimezone, current_date from DUAL; Sessiontimezone current_date-----------------------------------------05:00 2012-7-31 8:22:24sql> AL TER session SET Time_zone = ' -8:0 '; Session alteredsql> SELECT Sessiontimezone, current_date from DUAL; Sessiontimezone current_date-----------------------------------------08:00 2012-7-31 5:22:24 sql>

Current_timestamp
Current_timestamp (Precision)

--------------------------------------------------------------------------------

Current_timestamp returns the current date and time of the session time zone, with a value of TIMESTAMP with the ZONE type. The time zone offset reflects the current local time of the SQL session. If precision is omitted, the default is 6. The difference between this function and Localtimestamp is that Current_timestamp returns the TIMESTAMP with time ZONE value, while Localtimestamp returns the TIMESTAMP value.

The optional parameter precision specifies the decimal-second precision of the return time value.

Example 3: Demonstrates that Current_timestamp is sensitive to session time zones.

Sql> ALTER session SET time_zone = ' -5:0 '; Session alteredsql> ALTER Session SET Nls_date_format = ' yyyy-mm-dd HH24:MI:SS '; Session alteredsql> SELECT Sessiontimezone, current_timestamp from DUAL; sessiontimezone      Current_timestamp------------------------------------------------ -------------05:00               3 January-July-12 09.17.58.046000 morning -05:00 sql> sql> ALTER session SET time_zone = ' -8:0 '; Session alteredsql> SELECT Sessiontimezone, current_timestamp from DUAL; sessiontimezone      Current_timestamp------------------------------------------------ -------------08:00               3 January-July-12 06.21.11.500000 Morning -08:00 sql> when using a current_timestamp with a format mask, note the return value of the format mask matching function. For example, create the following table:

CREATE TABLE current_test (col1 TIMESTAMP with time ZONE); The following INSERT statement fails because the mask does not contain the time Current_timestap part of the ZONE function return type:

INSERT into Current_testvalues (To_timestamp_tz (Current_timestamp, ' Dd-mon-rr hh.mi. Ssxff PM '); The following statement uses the correct format mask:

INSERT into Current_testvalues (To_timestamp_tz (Current_timestamp, ' Dd-mon-rr hh.mi. Ssxff PM tzh:tzm '));

Dbtimezone

--------------------------------------------------------------------------------

Dbtimezone returns the database time zone value. The return type is the time zone offset (character type, format ' [+|-]tzh:tzm '), or time zone region name, which relies on the user specifying the database time zone value in the most recent CREATE database or ALTER database statement.

Example 4: Demo Dbtimezone. Suppose the database time zone is set to UTC time zone.

Sql> SELECT Dbtimezone from DUAL; DBTIME------+00:00 sql>

EXTRACT (DateTime)

--------------------------------------------------------------------------------

EXTRACT extracts and returns a specified date value from a date or time interval expression.

Example 5: Demonstrates extracting the year, month, and day from a specified date.

Sql> SELECT EXTRACT (year from date ' 2012-07-31 ') "EXTRACT Year", 2 EXTRACT (MONTH from date ' 2012-07-31 ') "extr ACT MONTH ", 3 EXTRACT (Day from DATE ' 2012-07-31 ')" EXTRACT Day "4 from DUAL; EXTRACT EXTRACT MONTH EXTRACT Day------------------------------------7 to Sql> Example 6: Demonstrates the number of employees who enter each month. In fact, this demo is better with the orders order form under Oracle OE Schema, but I don't have the table.

Sql> SELECT EXTRACT (month from hire_date) "Month",  2         COUNT ( hire_date) "No. of Hire "  3    from employees  4   GROUP by EXTRACT (month from hire_date)   5&NBSP ;  ORDER BY "No". of Hire "desc;      Month No. of Hire---------------------         3           17         1           14         2           13         6           11         8            9         4            7        12            7         7            7         5            6        10            6         9            5        11            5 rows selected sql> 

From_tz (Timestamp_value,time_zone_value)

--------------------------------------------------------------------------------

From_tz converts timestamp values and time zone values to TIMESTAMP with ZONE values.

Time_zone_value is a string formatted as ' Tzh:tzm ', or a character expression that returns a TZR format string, which is optional TZD format.

Example 7: Demonstrates a timestamp value that returns a TIMESTAMP with time ZONE.

Sql> SELECT From_tz (TIMESTAMP ' 2012-07-31 08:00:00 ', ' 3:00 ') from DUAL; From_tz ( TIMESTAMP ' 2012-07-3108--------------------------------------------------------------------------------3 January-July-12 08.00.00.000000000 Morning +03:00 sql>

Last_day (date)

--------------------------------------------------------------------------------

Last_day returns the date of the last day of the month. The last day of the month is defined by the session parameter Nls_calendar. The return type is always date, not the data type of the parameter date.

Example 8: Demonstrates how many days are left in the current month.

Sql> SELECT sysdate, 2 last_day (sysdate) "Last", 3 Last_day (sysdate)-sysdate ' days left ' 4 from DUAL; Sysdate last--------------------------------2012-8-1 2012-8-31 2 sql>

Localtimestamp
Localtimestamp (timestamp_precision)

--------------------------------------------------------------------------------

Localtimestamp returns the current date and time in the session time zone, with a type of TIMESTAMP. Localtimestamp returns the TIMESTAMP value, and Current_timestamp returns TIMESTAMP with time ZONE value.

Parameter timestamp_precision Optionally, specifying a time value that returns the decimal-second precision.

Example 9: Demonstrates the difference between Localtimestamp and Current_timestamp.

Sql> ALTER session SET time_zone = ' -5:00 '; Session alteredsql> SELECT Current_timestamp, localtimestamp from DUAL; current_timestamp                         Localtimestamp------------------------------------------------ --------------------------------January-August-12 10.06.21.203000 a.m. -05:00   January-August-12 10.06.21.203000 a.m. Sql> SQL > ALTER session SET time_zone = ' -8:00 '; Session alteredsql> SELECT Current_timestamp, localtimestamp from DUAL; current_timestamp                         Localtimestamp------------------------------------------------ --------------------------------January-August-12 07.06.27.281000 a.m. -08:00   January-August-12 07.06.27.281000 a.m. Sql> When using a localtimestamp with a format mask, note the return value of the format mask matching function. For example, create the following table:

CREATE TABLE local_test (col1 TIMESTAMP with local time ZONE); The following INSERT statement fails because the mask does not contain a time localtimestamp part of the ZONE function return type ：

INSERT into Local_testvalues (To_timestamp (Localtimestamp, ' Dd-mon-rr hh.mi. Ssxff '); The following statement uses the correct format mask:

INSERT into Local_testvalues (To_timestamp (Localtimestamp, ' Dd-mon-rr hh.mi. Ssxff PM '));

Months_between (DATE1,DATE2)

--------------------------------------------------------------------------------

Months_between return date How many months between Date1 and Date2. The month is defined by the session parameter Nls_calendar. If the date1 is later than Date2, the result is positive. If Date1 is earlier than date2, the result is negative. If the date1 and date2 dates are the same, or the last day of the month, the result is always an integer. Otherwise, the Oracle database calculates the decimal part based on 31 days a month, considering the time difference between Date1 and Date2.

Example 10: Demonstrates how many months between two dates are calculated.

Sql> SELECT Months_between (to_date (' 02-02-1995 ', ' mm-dd-yyyy '), 2 to_date (' 01-01-1995 ', ' mm-dd-     YYYY ')) "Months" 3 from DUAL; Months----------1.03225806 sql>

New_time (Date,timezone1,timezone2)

--------------------------------------------------------------------------------

New_time the time zone conversion of the date and time. Before using this function, you must set the reply parameter Nls_date_format to a 24-hour system, and the return type is always date, not the type of the parameter DATE.

Note:

This function is takes as input only a limited number of the time zones. You can have access to a much greater number of time zones by combining the From_tz function and the datetime expression.

Parameters Timezone1 and TimeZone2 can be any of the following text:

AST, ADT: Atlantic Standard or daylight saving time (Atlantic Standard or Daylight Time)
BST, BDT: Bering Sea Standard or daylight saving time (Bering Standard or Daylight Time) CST, CDT: Central Standard or daylight saving time (centralized Standard or daylight time)
EST, EDT: Eastern Standard or daylight saving time (eastern Standard or Daylight Time)
GMT: GMT (Greenwich Mean time)
HST, HDT: Alaska-Hawaiian Standard or daylight saving times (Alaska-hawaii Standard times or Daylight Time)
MST, MDT: Mountain Standard or daylight saving time (mountain Standard or daylight times)
NST: Newfoundland (Newfoundland)
Standard, PDT: Pacific Standard or daylight saving time (Pacific Standard or Daylight Time)
yst, YDT: Yukon Standard or daylight saving time (Yukon Standard or Daylight Time)
Example 11: Demo return equivalent to Taiping Ocean Standard Time (Pacific Standard times) of the Atlantic Standard Time (Atlantic Standard times).

Sql> ALTER session SET Nls_date_format = 2 ' yyyy-mm-dd HH24:MI:SS ';  Session alteredsql> SELECT New_time (to_date (' 2012-8-3 01:23:45 ', ' yyyy-mm-dd HH24:MI:SS '), 2 ' AST ', 3 ' PST ') ' New Date and Time ' 4 from DUAL; New Date and Time-----------------2012-8-2 21:23:45 sql>

Next_day (Date,char)

--------------------------------------------------------------------------------

Next_day returns the date of the first specified week after the specified date. The return type is always DATE. Parameter char must be the full name or abbreviation of week, week for the session date language. The return value has the same time, minute, and second as the parameter.

Example 12: Demonstrates the date of next Tuesday, February 2, 2012.

Sql> ALTER session SET Nls_date_language =american; Session Altered sql> SELECT next_day (' 02-feb-2012 ', ' Tuesday ') "NEXT Day" from DUAL; NEXT Day-----------2012-2-7 sql>

Numtodsinterval (n, ' Interval_unit ')
Numtoyminterval (N,interval_unit)

--------------------------------------------------------------------------------

Numtodsinterval converts n into INTERVAL day to SECOND literal meaning. Parameter n can be any number value or an expression that can be implicitly converted to a number value. Parameter interval_unit can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2 type. The value of the Interval_unit parameter specifies the unit of n, which must be the following string:

• ' Day '
• ' HOUR '
• ' MINUTE '
• ' SECOND '
Interval_unit parameter is not sensitive to case. The precision of the return value defaults to 9.

Numtoyminterval converts n into INTERVAL year to MONTH literal meaning. The value of the Interval_unit parameter specifies the unit of n, which must be the following string:

• ' Year '
• ' MONTH '
Example 13: Demonstrates the number of employees employed by the same manager in the last 100 days for each employee, using the Numtodsinterval calculation in the Count function.

sql> SELECT manager_id,  2         last_name,  3          hire_date,  4         COUNT (*) Over (PARTITION by manager_id ORDER by Hire_date RANGE numtodsinterval (MB, ' Day ') preceding) as t_count  5 &nbsp ;  from Employees; manager_id last_name                  hire_date      t_count--------------------------------------------------------        kochhar                    1989-9-21             1       De haan                    1993-1-13            1       Raphaely                   1994-12-7            1        kaufling                   1995-5-1             1        hartstein                  1996-2-17            1        weiss                      1996-7-18             1       russell                    1996-10-1             2       partners                   1997-1-5              2       errazuriz                  1997-3-10             2       fripp                      1997-4-10             3       Vollman                    1997-10-10            1       cambrault                  1999-10-15            1       mourgos                    1999-11-16            2       Zlotkey                    2000-1-29            2        whalen                   &nbsP 1987-9-17            1        mavris                     1994-6-7             3        higgins                    1994-6-7              3       baer                       1994-6-7              3       greenberg                  1994-8-17             4       102 Hunold                     1990-1-3              1 ... Selected Sql> Example 14: Demonstrates the total payroll for each employee employed in the past year, using numtodsinterval calculations in the SUM function.

sql> SELECT last_name,  2         hire_date,  3          salary,  4         SUM (Salary) Over (order by hire_date RANGE Numtoyminterval (1, "year") preceding) as t_sal  5    from Employees&nbs P 6   ORDER by last_name, hire_date; last_name                 HIRE_ date       salary      T_SAL----------------------------- ---------------------------abel                       1996-5-11     11000.00       61900ande                       2000-3-24      6400.00     106500atkinson                   1997-10-30     2800.00      160000austin                     1997-6-25      4800.00      134700baer                       1994-6-7      10000.00       36800baida                      1997-12-24     2900.00     180900Banda                       2000-4-21      6200.00     109400bates                      1999-3-24       7300.00     104900bell                       1996-2-4        4000.00      36300bernstein                  1997-3-24      9500.00      132200bissot                     1997-8-20      3300.00     145600bloom                      1998-3-23     10000.00     147300bull                       1997-2-20       4100.00      88000cabrio                     1999-2-7        3000.00     104100cambrault                  1998-12-9      7500.00      122500cambrault                  1999-10-15    11000.00      81400chen                        1997-9-28 &NBsp;    8200.00     153800chung                      1997-6-14       3800.00     133200colmenares                 1999-8-10      2500.00       76000davies                     1997-1-29      3100.00      98400. ... sql>  rows selected

ROUND (date)
ROUND (DATE,FMT)
TRUNC (date)
TRUNC (DATE,FMT)

--------------------------------------------------------------------------------

ROUND returns rounding to the date units specified by the format model FMT. This function is not sensitive to session parameter Nls_calendar. It executes according to the rules of the Gregorian calendar (Gregorian calendar). The return value is always DATE. If you omit FMT, the date four five to the nearest day. A date expression must be parsed into a date value.

Example 15: Demo on date October 27, 2012 using ROUND and TRUNC.

Sql> ALTER session SET Nls_date_language=american; Session alteredsql> SELECT ROUND (to_date (' 27-oct-12 '), ' year ') as "ROUND New Year", 2 TRUNC (' To_date '), ' year ') as "TRUNC New Year" 3 from DUAL; ROUND New Year TRUNC New Year----------------------------2013-1-1 2012-1-1 sql>

Sessiontimezone

--------------------------------------------------------------------------------

Sessiontimezone returns the time zone for the current session. Return type is time zone offset (format ' [+|] Tzh:tzm '), or time zone region name, depends on how the user specifies the session time zone value.

Example 16: Demonstrates returning the time zone for the current session.

Sql> SELECT Sessiontimezone from DUAL; Sessiontimezone---------------------------------------------------------------------------+08:00 sql>

SYS_EXTRACT_UTC (Datetime_with_timezone)

--------------------------------------------------------------------------------

SYS_EXTRACT_UTC extracts UTC (Coordinated Universal time-formerly Greenwich time) from a date value with a time zone offset or zone name.

Example 17: Demo SYS_EXTRACT_UTC.

Sql> SELECT sys_extract_utc (TIMESTAMP ' 2012-08-03 11:30:00.00-08:00 ') 2 from DUAL; SYS_EXTRACT_UTC ( TIMESTAMP '--------------------------------------------------------------------------------March-August-12 07.30.00.000000000 Afternoon sql>

Sysdate
Systimestamp

--------------------------------------------------------------------------------

Sysdate returns the date and time of the operating system on which the current database resides. The return type is DATE, and the format depends on the Nls_date_format initialization parameters. The function does not have any arguments. In a distributed SQL statement, this function returns the date and time of the local database operating system. You cannot use this function in the condition of a CHECK constraint.

Systimestamp returns the date of the operating system on which the database is located, with decimal seconds being the time zone. The return type is TIMESTAMP with time ZONE.

Example 18: Shows the date and time when the current operating system was returned.

Sql> Select To_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS ') "Now" from DUAL; Now-------------------2012-08-04 00:16:44 sql> Example 19: Demonstrates returning a system timestamp.

Sql> SELECT Systimestamp from DUAL; Systimestamp--------------------------------------------------------------------------------April-August-12 12.21.48.465000 Morning +08:00 sql> The following code shows how to explicitly specify decimal seconds (fractional seconds):

Sql> SELECT to_char (Systimestamp, ' sssss. FF ') from DUAL; To_char (Systimestamp, ' sssss. FF------------------------------01390.309000 sql>

To_char (DateTime)
To_char (DATETIME,FMT)
To_char (datetime,fmt, ' Nlsparam ')
To_char (interval)
To_char (INTERVAL,FMT)
To_char (interval,fmt, ' Nlsparam ')

--------------------------------------------------------------------------------

To_char (DateTime) Converts a date, TIMESTAMP, TIMESTAMP with time ZONE, or TIMESTAMP with a local time ZONE to the day of the specified format FMT Period. If FMT is omitted, the date is converted to a VARCHAR2 value:

Date value is converted to the value of the default date format
timestamp and TIMESTAMP with the local time ZONE values are converted to the default timestamp format
The timestamp with time ZONE value is converted to the value of a timestamp with the default time zone format
The ' nlsparam ' parameter specifies the language that returns the month, day name, and its abbreviation. Such as:

' Nls_date_language = LANGUAGE ' If ' nlsparam ' is omitted, the function uses the default date language in the session.

Example 20: Demo To_char.

Sql> CREATE TABLE date_tab (  2     ts_col      TIMESTAMP,  3     tsltz_col   TIMESTAMP with local time zone,  4     tstz_ col    TIMESTAMP with time ZONE); Table createdsql> ALTER session SET time_zone = ' -8:00 '; Session alteredsql> inserts into date_tab  2  values  3    (TIMESTAMP ' 1999-12-01 10:00:00 ',  4     TIMESTAMP ' 1999-12-01 10:00:00 ',  5     TIMESTAMP ' 1999-12-01 10:00:00 '); 1 row insertedsql> INSERT into date_tab  2  values  3    (TIMESTAMP ' 1999-12-02 10:00:00 -8:00 ',  4     TIMESTAMP ' 1999-12-02 10:00:00-8:00 ',  5     TIMESTAMP ' 1999-12-02 10:00:00-8:00 '); 1 row insertedsql> SELECT to_char (ts_col, ' dd-mon-yyyy HH24:MI:SSxFF ') as ts_date,  2    & Nbsp;    to_char (Tstz_col, ' dd-mon-yyyy HH24:MI:SSxFF tzh:tzm ') as tstz_date  3    from date_tab& nbsp 4   ORDER by Ts_date, tstz_date; ts_date                          tstz_date----------------------------------------------------------- ----------01-12ôâ-1999 10:00:00.000000    01-12ôâ-1999 10:00:00.000000-08:0002-12ôâ-1999 10:00:00.000000    02-12ôâ-1999 10:00:00.000000-08:00sql> SELECT sessiontimezone,  2          to_char (Tsltz_col, ' dd-mon-yyyy HH24:MI:SSxFF ') as tsltz  3     from date_tab  4   Sessiontimezone, Tsltz; Sessiontimezone Tsltz-----------------------------------------------08:00           01-12ôâ-1999 10:00:00.000000-08:00      &Nbsp;   02-12ôâ-1999 10:00:00.000000sql> ALTER session SET time_zone = ' -5:00 '; Session alteredsql> SELECT To_char (ts_col, ' dd-mon-yyyy HH24:MI:SSxFF ') as ts_col,  2    & nbsp;    to_char (Tstz_col, ' dd-mon-yyyy HH24:MI:SSxFF tzh:tzm ') as tstz_col  3    From date_tab  4  , Ts_col, Tstz_col; ts_col                           Tstz_col------------------------------------------------------ ---------------01-12ôâ-1999 10:00:00.000000    01-12ôâ-1999 10:00:00.000000-08:0002-12ôâ-1999 10:00:00.000000    02-12ôâ-1999 10:00:00.000000-08:00sql> SELECT sessiontimezone,  2          to_char (Tsltz_col, ' dd-mon-yyyy HH24:MI:SSxFF ') as tsltz_col  3     from date_tab  4   OrdeR by Sessiontimezone, Tsltz_col; Sessiontimezone Tsltz_col-----------------------------------------------05:00           01-12ôâ-1999 13:00:00.000000-05:00           02-12ôâ-1999 13:00:00.000000 sql> 

To_timestamp (char)
To_timestamp (CHAR,FMT)
To_timestamp (char,fmt, ' Nlsparam ')
To_timestamp_tz (char)
To_timestamp_tz (CHAR,FMT)
To_timestamp_tz (char,fmt, ' Nlsparam ')

--------------------------------------------------------------------------------

To_timestamp converts a value of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2 to a TIMESTAMP type. The FMT parameter specifies the format. The ' nlsparam ' parameter specifies the language.

The function does not directly support the CLOB type. However, CLOB can be passed as an implicit conversion of parameters.

To_timestamp_tz is the corresponding time zone function for To_timestamp.

Example 21: Demonstrates converting a string to a timestamp. The string is not the default TIMESTAMP format, so you must specify a format.

Sql> ALTER Session SET nls_date_language= ' American '; Session alteredsql> SELECT To_timestamp (' 10-sep-02 14:10:10.123000 ', ' Dd-mon-rr HH24:MI:SS. FF ') 2 from DUAL; To_timestamp (' 10-sep-0214:10:1-------------------------------------------------------------------------------- 10-sep-02 02.10.10.123000000 PM sql>

To_dsinterval (...)

--------------------------------------------------------------------------------

To_dsinterval converts a value of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2 to the INTERVAL day to SECOND type. To_dsinterval accepts parameters in the following two formats:

SQL interval format compatible with the SQL standard (ISO/IEC 9,075:2003)
ISO duration format compatible with the ISO 8601:2004 standard
In SQL format, the day is an integer between 0 and 999999999, and the hour is an integer between 0 and 23, and the minute and second between 0 and 59. Decimal seconds (Frac_secs) is the fractional portion of the second between. 0 to. 999999999. Days and small time can be multiple spaces. There can be extra spaces between the format elements.

In the ISO format, days, hours, minutes, and seconds are integers from 0 to 999999999. Decimal seconds (Frac_secs) is the fractional portion of the second between. 0 to. 999999999. No spaces are allowed in the value.

To_yminterval converts a value of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2 to the INTERVAL year to MONTH type. To_yminterval also accepts two forms:

SQL interval format compatible with the SQL standard (ISO/IEC 9,075:2003)
ISO duration format compatible with the ISO 8601:2004 standard
In SQL format, the year is an integer between 0 and 999999999, and the month is an integer between 0 and 11. Extra spaces are allowed between format elements.

In the ISO format, the year and month are integers from 0 to 999999999. Days, hours, minutes, seconds, and decimal seconds (frac_secs) are nonnegative integers that can be ignored if specified. Spaces are not allowed between values.

Example 22: Demo To_dsinterval and To_yminterval.

Sql> SELECT employee_id, last_name  2    from employees  3   WHERE hire_date + TO_D Sinterval (' 00:00:00 ') <= DATE ' 1990-01-01 '   4   order by employee_id; employee_id last_name------------------------------------        King         kochhar        whalensql> Select Hire_date, Hire_date + to_yminterval (' 01-02 ') "months"   2    from employees; hire_date   months----------------------1987-6-17   1988-8-171989-9-21   1990-11-211993-1-13   1994-3-131990-1-3    1991-3-31991-5-21   1992-7-211997-6-25   1998-8-251998-2-5    1999-4-51999-2-7    2000-4-71994-8-17   1995-10-171994-8-16   1995-10-161997-9-28   1998-11-281997-9-30    1998-11-301998-3-7    1999-5-71999-12-7   2001-2-71994-12-7   1996-2-71995-5-18   1996-7-181997-12-24  1999-2-241997-7-24   1998-9-241998-11-15  2000-1-151999-8-10   2000-10-10 ... sql>  rows selected

Tz_offset (...)

--------------------------------------------------------------------------------

Tz_offset returns the time zone offset.

Example 23: Demonstrates the time zone offset of the us/eastern time zone relative to UTC.

Sql> ALTER session SET nls_date_language = American; Session alteredsql> SELECT Tz_offset (' Us/eastern ') from DUAL; Tz_offset (' Us/eastern ')------------------------04:00 sql>

Note

--------------------------------------------------------------------------------

DATE data type
Date and time information is stored by the date type. Although date and time information can be displayed as a character and a numeric type, the date type has special related properties. For each DATE value, Oracle stores the following information: Century, year, month, day, hour, minute, and second.

You can specify the literal meaning of a date value, or use the To_date function to convert a character or numeric value to a date value. For example, the following is the way to express the date value, referring to the literal meaning of the date time.

Determine the default date value as follows:

• Year is the current year, returned by Sysdate
• Month is the current month, returned by Sysdate
* Day is 01 (the first day of the month)
• Time, minutes and seconds are all 0
sql> SELECT to_date (' The ', ' YYYY ') from DUAL; To_date ('---------01-may-05 sql>timestamp type
The TIMESTAMP type is an extension of the DATE type. It stores years, months, days, and sometimes, minutes, and seconds. This type is useful for storing exact time values. In particular the following TIMESTAMP types:

TIMESTAMP [(fractional_seconds_precision)] where fractional_seconds_precision optional, specifies the number of digits of Oracle storage for the decimal part of the DateTime field in seconds. When you create a column of that type, the range of values is 0 to 9. The default value is 6.

TIMESTAMP with time ZONE type
The TIMESTAMP with time ZONE is a variant of TIMESTAMP, and the value contains the TimeZone region name or time zone offset. Time zone cheapness is the difference (in hours and minutes) between local time and UTC (Coordinated Universal time-formerly Greenwich Mean times). This type is useful for gathering and evaluating information across geographic areas.

Specify the TIMESTAMP with time ZONE type as follows:

TIMESTAMP [(Fractional_seconds_precision)] with time zone, fractional_seconds_precision optional.

TIMESTAMP with the local time ZONE type
TIMESTAMP with the local time ZONE is another variant of TIMESTAMP, and the value contains the timezone offset. Unlike TIMESTAMP with time ZONE, which is stored in a database and is normalized as a database time zone, its timezone offsets are not stored as part of the column data. When the user retrieves the data, Oracle returns the user's local session time zone. The time zone is cheaper than the difference between UTC and local time. This data type is useful for displaying data information in a two-tier application (Two-tier application) client system. This data type is used in the two-tier _ application _ to display the latest information specified in the client system _ time zone.

The following specifies the TIMESTAMP with the local time ZONE type:

TIMESTAMP [(Fractional_seconds_precision)] with the local time zone, fractional_seconds_precision optional.

INTERVAL year to MONTH type
INTERVAL year to MONTH uses a date-time field for years and months to store for a period of time. This data type is useful for expressing differences between two dates as long as the year and month.

Specify INTERVAL year to MONTH as follows:

INTERVAL year [(Year_precision)] to month, year_precision the number of digits in the date-time field. The default is 2.

INTERVAL Day to SECOND type
INTERVAL Day to SECOND is stored for a period of days, hours, minutes, and seconds. This type is useful for the difference in precision between two datetime times.

This type is specified as follows:

INTERVAL Day [(day_precision)] to SECOND [(Fractional_seconds_precision)] where,

Day_precision is the number of digits in the day datetime field. Range is 0 to 9. Default is 2
Fractional_seconds_precision is the number of digits in the SECOND date-Time field decimal part. Range is 0 to 9. Default is 6