1. Oracle Truncate Table Sql
2. Oracle Truncate Vs Delete
3. Oracle Truncate Number
4. Oracle Truncate Aud$

TRUNC is an Oracle function used for truncating the value up to n decimal places after the decimal point. If we specify n as 0 then truncation happens only up to the digit before the decimal point.

Unlike the ROUND function, TRUNC function simply truncates the value and makes no changes to the value itself.

Syntax

Truncate will effectively just move the high water mark of a table back to 'zero' and either release allocated space back to dbafreespace or keep it for the segment. It just moves a pointer and says 'magically there is no data in this table'. It doesn't actually touch the data, it just says 'we don't have it anymore'.

• Oracle Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway. However, see Flashback below.
• The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day.
• Drop storage (the default) deallocates all the space above the minextents for the table truncate table. Drop all storage deallocates all space from the table Before using truncate you'll also need to check if there are any foreign keys pointing to this table.

Here,

Expression – Expression could be a column, numerical value or even date.

n – Number of digits after the decimal point up to which a value can truncated.

Let’s see some examples with TRUNC function.

1. Truncating a value

Lets use TRUNC function to truncate a few decimal values to see how it works. Lets see some queries:

Result:

2. TRUNC for truncating values in a Column

Here, we will be using Employee table from HR schema for this example:

Suppose, we want to perform a calculation on Salary table in HR column and truncate the calculated values then we use this query:

Result:

We use this query to first convert the salary in thousands and then we simply truncate all the values up to 2 digits.

3. Using TRUNC function with Dates

TRUNC function can also be used with dates to truncate date time values to a specified unit or format.

Syntax:

Here,

date – Date is the date time value that we will truncate.

Oracle Truncate Table Sql

format – We can specify the format in which we would like the date to be truncated. This is optional.

Some of the valid formats for Date are as follows:

Suppose, we have a date time value in this format ( as a character string):

’14-Mar-2012 16:30:18′

First, we need to convert this value to DATE data type as this value is considered a string of characters. For this we need to use, TO DATE function.

This function converts the character string to DATE data type in the format we want.

3.1 Using TRUNC function

Lets truncate the date to midnight:

Result:

In this example, we were able to truncate the date to midnight using 3 steps:

1. First, we used TO DATE function to convert date character string to Date value.
2. Then, we used TRUNC function to truncate that date. Since, we did not specify a date value format, Oracle by default truncated the value to midnight.
3. Finally, we used TO CHAR function to convert the date value back to character string format.

3.2 Using TRUNC function to get first day of the year

SYSDATE is an inbuilt function in Oracle which extracts the current date and time from the PC on which Oracle is installed.

Here, we use TRUNC function to find out the first date of the year:

Result:

We can also use a similar query for finding the first day of month, or quarter as well by specifying the required format.

In this tutorial, we learned how to use TRUNC function with decimals, columns and dates.

The Oracle TRUNC() function returns a DATE value truncated to a specified unit.

Syntax

The following shows the syntax of the Oracle TRUNC() function:

Arguments

The TRUNC() function accepts two arguments:

1) date
The date argument is a DATE value or an expression that evaluates to a DATE value that will be truncated.

2) format
The format argument determines the unit to which the date will be truncated.

The format argument is optional. Its default value is DD that instructs the TRUNC() function to truncate the date to midnight.

The following table illustrates valid values for the format argument:

Return value

The TRUNC() function returns a DATE value truncated to a specified unit.

Examples

Oracle Truncate Vs Delete

Let’s look at some examples of using the Oracle TRUNC() function.

A) Truncate a date value using default format

Consider the following date time value:

The following statement truncates the date value to midnight:

Output:

In this example,

• First, the TO_DATE() function converted a date string to a DATE value.
• Second, the TRUNC() function truncated the date. Because we did not pass the format argument, the TRUNC() function uses the default value that truncates the date to midnight.
• Third, the TO_CHAR() function formatted the result of the TRUNC() function.

B) Get the first day of the month of a date

The following statement returns the first day of the current month.

Output:

If you want to return the first day of the month of a specific date, you just need to use that date instead of the SYSDATE

Oracle Truncate Number

C) Get the first day of the quarter of a date

Similarly, you can get the first day of the current quarter:

Output:

In this example, we replaced the month ( MM) by quarter ( Q).

Oracle Truncate Aud$

In this tutorial, you have learned how to use the Oracle TRUNC() function to truncates a date value to a specified unit.