TRUNC, TRUNCATE

note

This article has additional information about the TRUNC and TRUNCATE SQL functions. For convertibility of this function, go to SQL functions  S - Z.

Truncating Numbers

• Convertible Function: For numeric arguments, TRUNC is a convertible function resolved by USoft and thus supported on all RDBMS platforms.

• Native Support: TRUNC is a native SQL function for numbers on Oracle and JDBC. TRUNCATE is a native SQL function on ODBC.

• SQL Server Conversion: SQL Server lacks a corresponding native function. When resolving numeric TRUNC() on SQL Server, USoft converts the expression to a ROUND() expression:

TRUNC( *n1* )

then the following is sent to SQL Server:

ROUND( *n1* - 0.5, 0 )

If 2 arguments are passed:

TRUNC( *n1, n2* )

then the following is sent to SQL Server:

ROUND( *n1* - 0.5 * POWER( 10.0, -(*n2*) ), *n2* )

Truncating Dates

• Oracle-Specific Behavior: USoft supports applying TRUNC() to date values on Oracle. This is not a convertible function; USoft passes the function directly to the Oracle RDBMS.

• Effect: On Oracle, TRUNC(date_value) strips the time information from the date, effectively setting the time to 00:00:00.

• Database-Independent Alternative: To achieve this same effect (stripping time from a date) in a database-independent fashion, use convertible USoft functions such as TIMESTAMP_TO_DATE(). This approach ensures your logic works across all supported RDBMS platforms, not just Oracle.