Table of contents
1.
Introduction
2.
What is the TRUNC Function?
2.1.
SQL
2.2.
Truncating Dates
2.3.
Practical Examples
3.
Frequently Asked Questions
3.1.
What is the TRUNC function in SQL?
3.2.
Can TRUNC function be used with dates?
3.3.
What is the difference between ROUND and TRUNC in SQL?
4.
Conclusion
Last Updated: Mar 27, 2024
Easy

Trunc Function in SQL

Author Gunjan Batra
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

SQL, or Structured Query Language, is a standard language for managing data held in a relational database management system. It's a robust language with a plethora of functions to help us in data manipulation and retrieval. One such function is the TRUNC function. 

Trunc function in sql

This article will take you through what the TRUNC function is, how to use it, and some practical examples of its use in SQL queries.

What is the TRUNC Function?

TRUNC stands for 'truncate.' In the context of SQL, this function is used to truncate a number to a certain number of decimal places or to truncate a date to a specified unit.

The syntax for using the TRUNC function with numbers is:

TRUNC(number, decimal_places)

When using the TRUNC function with dates, the syntax becomes:

TRUNC(date, format)

Truncating Numbers

The TRUNC function can be used with numbers to truncate them to a certain number of decimal places. For example, if you want to truncate the number 123.456 to one decimal place, you would use:

  • SQL

SQL

SELECT TRUNC(123.456, 1) FROM dual;

The output of this query would be 123.4.

Truncating Dates

The TRUNC function is also often used with dates. When used with a date, the TRUNC function can truncate the date to the specified format.

For example, to truncate the date to the year, you would use:

SELECT TRUNC(TO_DATE('13-OCT-21', 'DD-MON-YY'), 'YEAR') FROM dual;

The output of this query would be 01-JAN-21.

Output

Practical Examples

Let's look at a practical example using the TRUNC function in the context of a real database. Suppose you have a table named 'Orders' with the following data as given below in the orders table.

If you wanted to get the total income from each day, but without considering the cents, you could use the TRUNC function like this:

orders table
SELECT OrderDate, TRUNC(SUM(Quantity * Price)) as TotalIncome
FROM Orderss
GROUP BY OrderDate;

This will give you the total income per day, truncated to the nearest whole number.

output

Also read, Natural Join in SQL

Frequently Asked Questions

What is the TRUNC function in SQL?

The TRUNC function in SQL is used to truncate a number to a certain number of decimal places or truncate a date to a specified unit.

Can TRUNC function be used with dates?

Yes, the TRUNC function can be used with dates to truncate them to a specified format such as 'YEAR', 'MONTH', 'DAY', etc.

What is the difference between ROUND and TRUNC in SQL?

ROUND function rounds the number to the nearest value based on the decimal places, while TRUNC simply removes the extra decimal places without rounding.

Conclusion

In conclusion, the TRUNC function is a handy tool in SQL for handling both numbers and dates. Whether you're looking to round down a decimal number or truncate a date to a specific unit, TRUNC can be your go-to function. Understanding how to use this function can help you manipulate and retrieve data more efficiently, making your work with SQL even more effective.

If you want to learn more, then check out our articles:

 

Refer to our guided paths on Coding Ninjas Studio to upskill yourself in Data Structures and AlgorithmsCompetitive ProgrammingJavaScriptSystem Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio! But suppose you have just started your learning process and are looking for questions asked by tech giants like Amazon, Microsoft, Uber, etc. In that case, you must have a look at the problemsinterview experiences, and interview bundles for placement preparations.

Nevertheless, consider our paid courses to give your career an edge over others!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!

Live masterclass