Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

how to use trunc function in oracle sql

TRUNC Function in Oracle SQL – PLSQL

by

The TRUNC function in Oracle SQL / PLSQL is used to get a number truncated to specific number of decimal places.

Syntax for the TRUNC function in Oracle SQL / PLSQL is:

SELECT TRUNC(N,D)
FROM table_name;

  • N is the number to be truncated
  • D is the decimal places up to which N is to be truncated

Example 1:

SELECT TRUNC(1.23456,4)
FROM DUAL;

Will return “1.2345”

Example 2:

SELECT TRUNC(1.23456,3)
FROM DUAL;

Will return “1.234”

Example 3:

SELECT TRUNC(1.23456,2)
FROM DUAL;

Will return “1.23”

Example 4:

SELECT TRUNC(-1.23456,3)
FROM DUAL;

Will return “-1.234”

Example 5:

SELECT TRUNC(1.23456)
FROM DUAL;

Will return “1”

Oracle PL/SQL ROUND Function

by

Oracle ROUND FunctionOracle SQL ROUND Function returns a number rounded to specific number of decimal places. Oracle ROUND Function can also be used with dates to return the nearest day, month or year etc.

Oracle SQL ROUND Function Syntax

SELECT ROUND(N,D)
FROM table_name;

In the above Oracle ROUND Syntax;

  • N is the number to be rounded
  • D is the decimal places up to which N is to be rounded.

Oracle SQL ROUND Decimal – Using Positive Number Example

Oracle ROUND Function rounds a number to specific decimals.

For example, the SQL ROUND Decimal query below rounds the number ‘1.23456’to 4 places of decimal.

SELECT ROUND(1.23456,4) "ROUND DECIMAL"
FROM DUAL;

Above SQL ROUND Decimal query returns “1.2346”.

Note: We have aliased ROUND(1.23456,4) as “ROUND DECIMAL”.

Oracle SQL ROUND Decimal – Using Negative Number Example

Oracle ROUND, rounds even a negative number to specific decimals.

For example, the SQL ROUND Decimal query below rounds the number ‘-1.23456’to 3 places of decimal

SELECT ROUND(-1.23456,3) "Oracle ROUND DECIMAL"
FROM DUAL;

Above SQL ROUND Decimal query returns “-1.235”.

Note: We have aliased ROUND(-1.23456,3) as “Oracle ROUND DECIMAL”.

Oracle SQL ROUND Function – Using Date Example (Rounding to Year)

Oracle ROUND can also be used with dates.

For example, Oracle ROUND Function query below returns ‘1-Jan-2013’ as the nearest rounding to year.

SELECT ROUND(TO_DATE ('22-AUG-12'),'YEAR') "Oracle ROUND Function"
FROM DUAL;

More on Using Oracle PL/SQL ROUND Function With Dates.

Oracle SQL ROUND Function – Using Date Example (Rounding to Month)

For example, Oracle ROUND Function query below returns ‘1-September-2012’ as the nearest rounding to month.

SELECT ROUND(TO_DATE ('22-AUG-12'),'MONTH') "Oracle ROUND Function"
FROM DUAL;

More on Using Oracle PL/SQL ROUND Function With Dates.

TRUNC Function in Oracle SQL – PLSQL

by

The TRUNC function in Oracle SQL / PLSQL is used to return the date truncated to a specific instance.

Syntax for the TRUNC function in Oracle SQL / PLSQL is:

SELECT TRUNC(date,[format])
FROM table_name;

Here in the above statement:

  • ‘date’ is the date that is passed to be truncated
  • ‘format’ is the unit up to which the rounding is to be done. If format is not given or omitted then the date is truncated to the nearest day. Any hours, minutes and seconds will be truncated off.

Considering sysdate as November 2nd 2012

Observe the table below to understand what TRUNC function does when passed with different format parameters.

Parameter_Passed Explanation Select_Query Output
YEAR Truncates to nearest year 
SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL;
 1/1/2012
YYYY Truncates to nearest year 
SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;
 1/1/2012
YYY Truncates to nearest year 
SELECT TRUNC(SYSDATE,'YYY') FROM DUAL;
 1/1/2012
YY Truncates to nearest year 
SELECT TRUNC(SYSDATE,'YY') FROM DUAL;
 1/1/2012
Y Truncates to nearest year 
SELECT TRUNC(SYSDATE,'Y') FROM DUAL;
 1/1/2012
SYYYY Truncates to nearest year 
SELECT TRUNC(SYSDATE,'SYYYY') FROM DUAL;
 1/1/2012
SYEAR Truncates to nearest year 
SELECT TRUNC(SYSDATE,'SYEAR') FROM DUAL;
 1/1/2012
IYYY Truncates to nearest year based on ISO standard 
SELECT TRUNC(SYSDATE,'IYYY') FROM DUAL;
 1/2/2012
IYY Truncates to nearest year based on ISO standard 
SELECT TRUNC(SYSDATE,'IYY') FROM DUAL;
 1/2/2012
IY Truncates to nearest year based on ISO standard 
SELECT TRUNC(SYSDATE,'IY') FROM DUAL;
 1/2/2012
I Truncates to nearest year based on ISO standard 
SELECT TRUNC(SYSDATE,'I') FROM DUAL;
 1/2/2012
Q TRUNC to the nearest Quarter of Year (1,2,3,4) January-March 1st quarter 
SELECT TRUNC(SYSDATE,'Q') FROM DUAL;
 10/1/2012
MM Truncates to the 16th day of Month 
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;
 11/1/2012
MON Truncates to the 16th day of Month 
SELECT TRUNC(SYSDATE,'MON') FROM DUAL;
 11/1/2012
MONTH Truncates to the 16th day of Month 
SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL;
 11/1/2012
RM Truncates to the 16th day of Month 
SELECT TRUNC(SYSDATE,'RM') FROM DUAL;
 11/1/2012
WW Same day of the week as 1st day of year 
SELECT TRUNC(SYSDATE,'WW') FROM DUAL;
 10/28/2012
W Same day of the week as 1st day of month 
SELECT TRUNC(SYSDATE,'W') FROM DUAL;
 11/1/2012
IW Same day of the week as 1st day of ISO year 
SELECT TRUNC(SYSDATE,'IW') FROM DUAL;
 10/29/2012
D Starting Day of Week 
SELECT TRUNC(SYSDATE,'D') FROM DUAL;
 10/28/2012
DY Starting Day of Week 
SELECT TRUNC(SYSDATE,'DY') FROM DUAL;
 10/28/2012
DAY Starting Day of Week 
SELECT TRUNC(SYSDATE,'DAY') FROM DUAL;
 10/28/2012
DD Nearest Day 
SELECT TRUNC(SYSDATE,'DD') FROM DUAL;
 11/2/2012
DDD Nearest Day 
SELECT TRUNC(SYSDATE,'DDD') FROM DUAL;
 11/2/2012
J Nearest Day 
SELECT TRUNC(SYSDATE,'J') FROM DUAL;
 11/2/2012
HH Hour of Day (1-12) 
SELECT TRUNC(SYSDATE,'HH') FROM DUAL;
 11/2/2012 4:00:00 PM
HH12 Hour of Day (1-12) 
SELECT TRUNC(SYSDATE,'HH12') FROM DUAL;
 11/2/2012 4:00:00 PM
HH24 Hour of Day (0-23) 
SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
 11/2/2012 4:00:00 PM
MI Minute (0-59) 
SELECT TRUNC(SYSDATE,'MI') FROM DUAL;
 11/2/2012 4:47:00 PM

Example 1:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'YEAR')
FROM DUAL;

Will return 1/1/2012

Example 2:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'Q')
FROM DUAL;

Will return 7/1/2012

Example 3:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'MONTH')
FROM DUAL;

Will return 8/1/2012

Example 4:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'DDD')
FROM DUAL;

Will return 8/22/2012

Example 5:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'DAY')
FROM DUAL;

Will return 8/19/2012