CODEWITHSUNDEEP
sqloracle-databasetruncate
javaTwilight
javaTwilight

Reputation: 35

When and why does the TRUNC(DATE, [FORMAT]) used in the real world

I am getting into Oracle database. I came across the TRUNC(DATE, [FMT]) function. I am not really clear on it except it seems to return the beginning value of some sort?

Can somebody educate me on it? When or what would it be used for at work, or why somebody might want to use the function?

Upvotes: 2

Views: 5102

Answers (3)

SandPiper
SandPiper

Reputation: 2906

Another thing it is useful for is to get the time component of the current day. I use an expression like this all the time:

SELECT sysdate - trunc(sysdate) AS TodaysTime FROM DUAL

Because the system date is stored in a decimal format (e.g. sysdate = 42651.2426897456) and the integer value corresponds to midnight, I can use the above statement to get only the decimal portion (e.g. TodaysTime = 0.2426897456, or just before 6 AM).

There may be easier ways to do this, but in my applications this has been the easiest as I frequently need to work with only the day's time component.

Upvotes: 0

Momus
Momus

Reputation: 394

A real world example would be if you wanted to aggregate results from a table by year. You could use the TRUNC function like this:

SELECT TRUNC(my_date, 'YEAR') the_year, count(*)
FROM some_table
GROUP BY TRUNC(my_date, 'YEAR');

...which would return a set of results with the first column the date truncated to the beginning of the year and the second column a count of all the records with dates within that year.

the_year, count(*)
_________________
01-JAN-12, 543
01-JAN-13, 1268
01-JAN-14, 1134
01-JAN-15, 1765

There are obviously other ways to achieve the same thing, but this is a real world example of how you might use TRUNC.

Another might be if you are comparing dates and you only want to use a certain degree of precision. If you have a timestamp column and you want all the records for today, you could select based on a range where the timestamp is greater than midnight yesterday and less than midnight today, or you could select where the timestamp, truncated to the DATE, is equal to today.

https://docs.oracle.com/cd/E29805_01/server.230/es_eql/src/cdfp_analytics_lang_trunc.html

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

Try this query to know when it might be usefull:

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

select  sysdate,
        trunc( sysdate, 'mi' ) As beginning_of_current_minute,
        trunc( sysdate, 'mi' ) As beginning_of_current_hour,
        trunc( sysdate, 'dd' ) As beginning_of_current_day,
        trunc( sysdate, 'iw' ) As beginning_of_current_week,
        trunc( sysdate, 'mm' ) As beginning_of_current_month,
        trunc( sysdate, 'q' ) As beginning_of_current_Quarter,
        trunc( sysdate, 'y' ) As beginning_of_current_Year
FROM dual;

An example - you want to get all orders starting from the beginning of the current week:

SELECT *
FROM ORDERS
WHERE order_date >= trunc( sysdate, 'iw' )

Upvotes: 2

Related Questions