![]() Previous |
![]() Next |
The OLAP DML DATETIME data type corresponds to the SQL DATE
data type. As such, the format and language of DATETIME
values are controlled by the settings of the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE options. The DATETIME
data type is supported by Oracle Database standard libraries and operates the same way in the OLAP DML as it does the DATE
data type in SQL.
You can specify a DATETIME
value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE
function.
To specify a DATETIME
value as a literal, you must use the Gregorian calendar. You can specify an ANSI literal, as shown in this example:
DATETIME '1998-12-25'
The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD
').
Alternatively you can specify a DATETIME value us the TO_DATE function and include, as in the following example:
TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')
The default date format template for an Oracle DATETIME
value is specified by the initialization parameter NLS_DATE_FORMAT
. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation.
Oracle automatically converts character values that are in the default datetime format into datetime values when they are used in datetime expressions.
If you specify a datetime value without a time component, then the default time is midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively). If you specify a datetime value without a date, then the default date is the first day of the current month.
Values of DATETIME
always contain both the date and time fields. Therefore, if you use DATETIME
values in an expression, you must either specify the time field in your query or ensure that the time fields in the DATETIME
values are set to midnight. Otherwise, Oracle may not return the results you expect. You can use the TRUNC
(date) function to set the time field to midnight, or you can include a greater-than or less-than condition in the query instead of an equality or inequality condition. However, if the expression contains DATETIME values other than midnight, then you must filter out the time fields in the query to get the correct result.
The date function SYSDATE
returns the current system date and time. The function CURRENT_DATE
returns the current session date. For information on SYSDATE
, the TO_*
datetime functions, and the default date format, see "Datetime functions" and the DATE_FORMAT command.