Oracle Truncate

Posted on 16-04-2021 by admin

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

The SQL TRUNCATE TABLE statement is a useful statement for removing data in SQL. I’ll explain what the statement does and show you some examples in this article. This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.
The Oracle TRUNC function returns a DATE value truncated to a specified unit.
Oracle 10g: truncate string. Ask Question Asked 2 years, 3 months ago. Active 2 years, 3 months ago. Viewed 8k times 0. I'm having a query that append a string value from multiple rows and insert the result in a new table. I would like to limit the String length that is inserted.
TRUNC removes the decimal part for both negatives and positives. Therefore, TRUNC(-x) is equal to -TRUNC(x). ROUND returns the closest value and when both upper and lower values are equidistant, it rounds up to the higher positive value or the lower negative value, i.e.

Syntax

The Oracle/PLSQL TRUNC function returns a date truncated to a specific unit of measure.

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

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:

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

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

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).

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

Coments are closed