![]() Previous |
![]() Next |
The TRUNCATE (datetime) function returns date with the time portion of the day truncated to the unit specified by the format model.
Return Value
DATETIME
Syntax
TRUNC (datetime_exp, [fmt])
Parameters
A datetime expression that identifies a date and time number.
A text expression that specifies a format model shown in Table: Datetime Format Templates for the ROUND and TRUNC Date Functions. A format model indicates how the date and time number should be truncated. If you omit fmt
, then date
is truncated to the nearest day.
Examples
Truncating to the Nearest Year
When the value of the NLS_DATE_FORMAT option is DD-MON-YY
, then this statement:
SHOW TRUNC (TO_DATE('27-OCT-92'),'YEAR')
returns this value:
01-JAN-92
Truncating Using Different Formats
Assume the following option values, variables, and values are in your analytic workspace.
SHOW NLS_DATE_FORMAT DD-MON-RR DEFINE MYDATETIME VARIABLE DATETIME DATE_FORMAT = 'MON-RRRR-DD-HH24' mydatetime = CURRENT_TIMESTAMP SHOW mydatetime = 'AUG-2006-07-14'
As the following SHOW statements illustrate, the value you specify for the format argument of TRUNCATE function determines the value returned by that function.
SHOW TRUNCATE(mydatetime, 'MON') 01-AUG-06 SHOW TRUNCATE(mydatetime, 'DD') 07-AUG-06 SHOW TRUNCATE(mydatetime) = 'AUG-2006-07-00'