Purpose of the Oracle TRUNC Function
The purpose of the Oracle TRUNC function is to truncate either a number or a date value.
The TRUNC function works on two different data types – number or date. This means you can provide it with either of these two data types, and the TRUNC function will perform slightly differently and give you a result for that data type.
If you supply it a date data type, then it will return the portion of that value as specified by your format model. If you supply it a numeric data type, it will round the number to the specified number of decimal places.
Let’s take a look at how this is used.
The syntax of the TRUNC function is slightly different, depending on which data format you’re supplying. In both cases, there are two parameters.
Using a date value, the syntax is:
TRUNC( date, fmt )
Using a number, the syntax is:
TRUNC( number, decimals )
Let’s take a look at the parameters for this function.
The parameters of the Oracle TRUNC function for dates are:
- date (mandatory): This is the date or datetime value that is to be truncated or reduced to.
- fmt (optional): This is the format that the date value is to be truncated to. If it is not specified, then the function will truncate the date value to the nearest day. See below for the valid format parameters that can be used.
The parameters of the Oracle TRUNC function for numbers are:
- number (mandatory): This is the numeric value that is to be truncated.
- decimals (optional): This is the number of places to the left or right of the decimal point to truncate the number value. If it is positive, digits to the right are truncated, and if it is negative, digits to the left are truncated. If it is omitted, number is then truncated to a whole number (0 decimal places).
If you use the date version of the TRUNC function, then the value returned is always of datatype date, even if you use a different datetime datatype as the parameter.
If you use the number version of the TRUNC function, then the returned value is the same as the number parameter if you don’t specify the decimals. If you do specify the decimals, then it returns a data type of NUMBER.
Available Formats for Date TRUNC
These are the available format values for using the TRUNC function on dates. You can specify these as part of the second parameter when using the TRUNC function on dates.
|Unit||Valid Format Parameters|
|Year||SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y|
|ISO Year||IYYY, IY, I|
|Month||MONTH, MON, MM, RM|
|Day||DDD, DD, J|
|Start day of the week||DAY, DY, D|
|Hour||HH, HH12, HH24|
Using Oracle TRUNC on SYSDATE
The Oracle TRUNC function is often used on the SYSDATE keyword. It’s something I do quite often. It’s useful to get the current date, without the time, by using the SYSDATE keyword.
You can use it by simply running:
See below for more examples about using TRUNC with SYSDATE.
Can You Use Oracle TRUNC on a TIMESTAMP?
Yes, you can. The DATE version of the TRUNC function accepts any date data type, including timestamp. You can then truncate it to the format you need.
As of Oracle version 9 (version 188.8.131.52 I believe), you can perform TRUNC on timestamp. Before this version, you got an error (ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP).
If you are unable to use TRUNC on timestamps in your version, you can CAST it to a DATE first:
TRUNC(CAST(TIMESTAMP as DATE))
See below for some examples on using TRUNC with TIMESTAMP.
Can You Use Oracle TRUNC on a STRING?
No, you can’t use the Oracle TRUNC function on a string data type. If you want to remove characters from a string data type, you can use the SUBSTR function
Examples of the Oracle TRUNC Function
Here are some examples of the TRUNC function. I find that examples are the best way for me to learn about code, even with the explanation above.
These examples use a table called “datelist” that I created using the following code:
CREATE TABLE datelist ( datevalue DATE, timestampvalue TIMESTAMP );
INSERT INTO datelist (datevalue, timestampvalue) VALUES (SYSDATE, SYSTIMESTAMP);
SELECT datevalue, timestampvalue FROM datelist;
|26/MAR/15||26/MAR/15 05:58:01.378000000 AM|
And yes, it is about 6AM when I’m writing this post!
This example shows how to use TRUNC on a date to truncate it to a year.
SELECT TRUNC(datevalue, 'YEAR') AS TRUNCVALUE FROM datelist;
It shows the date value converted to a year, which is the first day of the first month of the year.
This example uses TRUNC on a date to truncate it into a month.
SELECT TRUNC(datevalue, 'MONTH') AS TRUNCVALUE FROM datelist;
It shows a similar result, converting the date to a month and year value, which changes the day to the first of that month.
This example uses TRUNC on a date to truncate it to a day.
SELECT TRUNC(datevalue, 'DD') AS TRUNCVALUE FROM datelist;
It looks to be the same value that is returned, because the provided value is already a day/month/year type.
This example uses TRUNC to show the date value as an IW format (which is the week number in a year).
SELECT TRUNC(datevalue, 'IW') AS TRUNCVALUE FROM datelist;
It has truncated the value back to March 23, which is the Monday of the same week.
This example shows how to use TRUNC on SYSDATE.
SELECT TRUNC(SYSDATE) AS TRUNCVALUE FROM dual;
It looks to be the same value that is returned, because SYSDATE is already in that format.
This example uses TRUNC on a TIMESTAMP value.
SELECT TRUNC(timestampvalue) AS TRUNCVALUE FROM datelist;
As you can see, it has removed the time value from the timestamp and just returned a date.
This example uses a TIMESTAMP and shows it as months.
SELECT TRUNC(timestampvalue, 'MM') AS TRUNCVALUE FROM datelist;
This shows the timestamp value truncated to the month, which is March.
This example shows TRUNC using a number.
SELECT TRUNC(5718.46987) AS TRUNCVALUE FROM dual;
The number is truncated to a whole number. The decimals are removed, which is done without rounding.
This example truncates the number to 2 places.
SELECT TRUNC(5718.46987, 2) AS TRUNCVALUE FROM dual;
The result shows the original number with two decimal places, without any rounding.
This example truncates the number to 3 places to the left of the decimal.
SELECT TRUNC(5718.46987, -3) AS TRUNCVALUE FROM dual;
This value is the result of truncating the 3 decimal places, so it only shows the thousands value.
This example truncates the number using a decimal value of 0.
SELECT TRUNC(5718.46987, 0) AS TRUNCVALUE FROM dual;
This is the same as providing no decimal places, as it rounds to a whole number.
Some functions which are similar to the Oracle TRUNC function are:
- TRIM – removes the leading and trailing characters from a string
- SUBSTR – returns a part of the provided value, based on the specified position and length
- ROUND – rounds a value to the specified number of places.
You can find a full list of Oracle functions here.
Image courtesy of digitalart / FreeDigitalPhotos.net
Oracle SQL Function Cheat Sheet
Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!