Appearance

Reference>Meta Database>Function Reference>TRUNC

TRUNC ​

This function can be used for numerical values, dates, and timestamps to truncate to the specified part of expr.

TRUNC(NUMBER) ​

Truncates expr to the nearest (or equal) integer closer to NULL, or to the nearest equal or smaller value with the specified number of decimal places equal to scale.

Syntax ​

sql
TRUNC(expr[,scale])

Arguments ​

expr: Any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type.

scale: The number of decimal places to include in the output. The default value is NULL. If scale = NULL, all decimal places are removed (effectively making it an integer).

Return Values ​

  • If scale is not specified, the function returns the same data type as the numeric data type of expr.

  • If scale is specified, the function returns the return value of data type NUMBER.

  • If scale < 0, the output scale is 0.

Example: TRUNC(3.14, 1) returns NUMBER(4, 1); TRUNC(3.14, 0) returns NUMBER(4, 0); TRUNC(33.33, -1) returns NUMBER(5, 0).

Usage Notes ​

  • If scale < 0, the number of digits before the decimal point to which the number is adjusted is specified. Example: scale = -2, the result is a multiple of 100.

  • If scale is greater than the scale of expr, the function has no effect.

  • If either expr or scale is NULL, the result is NULL.

  • Truncation of decimal places is towards 0, not towards the smaller number. Example: TRUNC(-9.6) = -9 and not -10.

TRUNC(TIMESTAMP) ​

The function returns the date with the time portion of the day truncated to the unit specified by the format model fmt. TRUNC is synonymous with the FLOOR function for dates and timestamps.

Syntax ​

sql
TRUNC(expr[,fmt])

Arguments ​

expr: Date, timestamp, or an expression that can be resolved to a TIMESTAMP value.

fmt: The return value format. For information on valid fmt specifications, see the table for the ROUND(TIMESTAMP) function.

Return Values ​

The returned value is of data type TIMESTAMP.

Usage Notes ​

If fmt is not specified, the default value DD is used, and the returned value is the date truncated to the day with a time of midnight.

Example 1 | TRUNC(NUMBER) ​

sql
SELECT 'trunc(734.9191)' as function, trunc(734.9191       ) as result
union all
SELECT 'trunc(-734.9191)' as function, trunc(-734.9191      ) as result 
union all
SELECT 'trunc(734.9191, -1)' as function, trunc(734.9191, -1   ) as result
union all
SELECT 'trunc(734.9191, 0)' as function, trunc(734.9191, 0    ) as result
union all
SELECT 'trunc(734.9191, 1)' as function, trunc(734.9191, 1    ) as result
union all
SELECT 'trunc(734.9191, NULL)' as function, trunc(734.9191, NULL ) as result
union all
SELECT 'trunc(-9.3, 0)' as function, trunc(-9.3,     0    ) as result
       ;
+-----------------------+--------+
| FUNCTION              | RESULT |
+-----------------------+--------+
| trunc(734.9191)       | 734    |
| trunc(-734.9191)      | -734   |
| trunc(734.9191, -1)   | 730    |
| trunc(734.9191, 0)    | 734    |
| trunc(734.9191, 1)    | 734.9  |
| trunc(734.9191, NULL) | NULL   |
| trunc(-9.3, 0)        | -9     |
+-----------------------+--------+

Example 2 | TRUNC(TIMESTAMP) ​

sql
select  trunc(to_date('19-NOV-2023'),'YEAR') as result1, 
        trunc(to_date('19-NOV-2023'),'MONTH') as result2,
        trunc(to_date('19-NOV-2023'),'DAY') as result3, 
        trunc(to_date('19-NOV-2023')) as result4;
+--------------------------+--------------------------+--------------------------+--------------------------+
| RESULT1                  | RESULT2                  | RESULT3                  | RESULT4                  |
+--------------------------+--------------------------+--------------------------+--------------------------+
| 2022-12-31T22:00:00.000Z | 2023-10-31T22:00:00.000Z | 2023-11-18T22:00:00.000Z | 2023-11-18T22:00:00.000Z |
+--------------------------+--------------------------+--------------------------+--------------------------+
sql
WITH dates AS (   
  SELECT date'2024-01-01' col1 union   
  SELECT date'2024-01-10' col1  union   
  SELECT date'2024-02-01' col1 union   
  SELECT timestamp'2024-03-03 23:45:00' col1  union   
  SELECT timestamp'2024-04-11 12:34:56' col1     
)   
SELECT col1 "original date",   
       trunc(col1) "nearest day",   
       trunc(col1, 'ww') "nearest week", 
       trunc(col1, 'iw') "start of week",   
       trunc(col1, 'mm') "start of month",   
       trunc(col1, 'year') "start of year"   
FROM dates;
+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
| original date            | nearest day              | nearest week             | start of week            | start of month           | start of year            |
+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
| 2023-12-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z |
| 2024-01-09T22:00:00.000Z | 2024-01-09T22:00:00.000Z | 2024-01-07T22:00:00.000Z | 2024-01-07T22:00:00.000Z | 2023-12-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z |
| 2024-01-31T22:00:00.000Z | 2024-01-31T22:00:00.000Z | 2024-01-28T22:00:00.000Z | 2024-01-28T22:00:00.000Z | 2024-01-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z |
| 2024-03-03T21:45:00.000Z | 2024-03-02T22:00:00.000Z | 2024-02-25T22:00:00.000Z | 2024-02-25T22:00:00.000Z | 2024-02-29T22:00:00.000Z | 2023-12-31T22:00:00.000Z |
| 2024-04-11T10:34:56.000Z | 2024-04-10T22:00:00.000Z | 2024-04-07T22:00:00.000Z | 2024-04-07T22:00:00.000Z | 2024-03-31T22:00:00.000Z | 2023-12-31T22:00:00.000Z |
+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+--------------------------+
© 2025 MetaKraftwerk GmbH. All Rights Reserved.