Purpose of trunc(date) in oracle

Hello Experts,
I am trying to understand the purpose/need of the TRUNC(date). I went through the documentation in Oracle and the example :

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92

I am not able to understand why Oct becomes Jan here. Please help me understand this behavior and any real scenarios where this method is used.
sukhoi35Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
that produces the 1st day of the year from that date.

"trunc" is short for "truncate" and that function is used quite often in Oracle queries

for example when you store a date with the current time as well as date yo may need just the date e.g. TRUNC(datecolumn)

You can derive several different truncated dates e.g.

select
      TRUNC(TO_DATE('22-AUG-2018'), 'YEAR') start_year
    , TRUNC(TO_DATE('22-AUG-2018'), 'Q') start_quarter
    , TRUNC(TO_DATE('22-AUG-2018'), 'MONTH') start_month
    , TRUNC(TO_DATE('22-AUG-2018'), 'WW') start_week
    , TRUNC(TO_DATE('2018-08-22 13:14:15','yyyy-mm-dd hh24:mi:ss')) start_date
from dual

+---+---------------------+---------------------+---------------------+---------------------+---------------------+
|   |     START_YEAR      |    START_QUARTER    |     START_MONTH     |     START_WEEK      |     START_DATE      |
+---+---------------------+---------------------+---------------------+---------------------+---------------------+
| 1 | 01.01.2018 00:00:00 | 01.07.2018 00:00:00 | 01.08.2018 00:00:00 | 20.08.2018 00:00:00 | 22.08.2018 00:00:00 |
+---+---------------------+---------------------+---------------------+---------------------+---------------------+

Open in new window

0
sukhoi35Author Commented:
"for example when you store a date with the current time as well as date yo may need just the date"

So, do you mean for '27-OCT-92 22:00:00', we get '27-OCT-92' in return? i.e. removing the time

But why are we changing that to 1st/Jan/92?
0
PortletPaulEE Topic AdvisorCommented:
Use TRUNC() on dates to arrive at "the start of something"

start of the year
start of the quarter
start of the month
start of the week
start of the day
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Pawan KumarDatabase ExpertCommented:
Please find the detailed explanation for the function and the REAL life scenario example.(at the end)

>>I am not able to understand why Oct becomes Jan here.

TRUNC is a function which accepts two parameters -> DATE/DATE wth TIME and Format and it will return a DATE VALUE. The return value is based on the format we will provide in the function. Also note that the format is also optional. IF we do not supply the format the trunc function will return the date with time 00:00:00

TRUNC ( date [, format ] ) --> OPTIONAL [, format ]

The Format can be one of the below if supplied.

--

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI
--

Open in new window


Refer more from here - https://www.techonthenet.com/oracle/functions/trunc_date.php

E.g.

--

SELECT SYSDATE currentDate FROM dual

--

Open in new window


OUTPUT

--

	CURRENTDATE
	
1	08.12.2017 05:12:08

--

Open in new window


If we run above it wil give me CURRENTDATE -> 08.12.2017 05:12:08, that is current date and time. Now check the other case.

CASE 2. GET TODAYS DATE WITH OUT TIME REMOVE TIME FROM THE DATE with TIME.

--

SELECT TRUNC(SYSDATE) RemovedTimeFromDATE FROM dual

--

Open in new window


OUTPUT

--
 	REMOVEDTIMEFROMDATE
	
1	08.12.2017 00:00:00

--

Open in new window


CASE 3. GET THE FIRST DATE OF THE YEAR

--

SELECT TRUNC(SYSDATE,'YEAR') FirstDayoftheYearWithTIMEZero FROM dual

--

Open in new window


OUTPUT

--

 	FirstDayoftheYearWithTIMEZero
	
1	01.01.2017 00:00:00

--

Open in new window


Now this function TRUNC(SYSDATE,'YEAR') will give you first date of the Year as per the format we have given. The format decides what the function will return. So for Year format it will return the first date of that year with time 00:00:00. Basically it changes any time to 00:00:00


>>"for example when you store a date with the current time as well as date yo may need just the date"
So, do you mean for '27-OCT-92 22:00:00', we get '27-OCT-92' in return? i.e. removing the time But why are we changing that to 1st/Jan/92?



If you want to remove the time completely from the datetime then you should use to_char() function. Also note that it will return the string type not the date. Note we should use this for display purpose and trunc for the calculations purpose.

Please see example below.

--

SELECT TO_CHAR(sysdate, 'yyyy/mm/dd') removeTimecompletetly from dual\\

--

Open in new window


OUTPUT

--


 	REMOVETIMECOMPLETETLY
	
1	2017/12/08

--

Open in new window


>>Please help me understand this behavior and any real scenarios where this method is used.

Now lets talk few real scenarios.

Assume you have orders table and someone told you to get the orders for this year. How will you do it?

Sample table and Data insertion.

CREATE TABLE Orders_p
(
   OrderId INT
  ,OrderDate TIMESTAMP  
);
  
INSERT INTO Orders_p SELECT 1 , SYSDATE  FROM DUAL;
INSERT INTO Orders_p SELECT 2 , SYSDATE  FROM DUAL;
INSERT INTO Orders_p SELECT 3 , add_months(SYSDATE , 13)  FROM DUAL;
INSERT INTO Orders_p SELECT 4 , add_months(SYSDATE , 14)  FROM DUAL;
INSERT INTO Orders_p SELECT 5 , SYSDATE  FROM DUAL;
INSERT INTO Orders_p SELECT 6 , add_months(SYSDATE , 50)  FROM DUAL;
INSERT INTO Orders_p SELECT 7 , add_months(SYSDATE , 50)  FROM DUAL;
INSERT INTO Orders_p SELECT 8 , add_months(SYSDATE , 50)  FROM DUAL;
INSERT INTO Orders_p SELECT 9 , SYSDATE  FROM DUAL;

SELECT * FROM Orders_p;

Open in new window


CURRENT DATA

| ORDERID |             ORDERDATE |
|---------|-----------------------|
|       1 | 2017-12-08 04:52:36.0 |
|       2 | 2017-12-08 04:52:36.0 |
|       3 | 2019-01-08 04:52:36.0 |
|       4 | 2019-02-08 04:52:36.0 |
|       5 | 2017-12-08 04:52:36.0 |
|       6 | 2022-02-08 04:52:36.0 |
|       7 | 2022-02-08 04:52:36.0 |
|       8 | 2022-02-08 04:52:36.0 |
|       9 | 2017-12-08 04:52:36.0 |

Open in new window


Now if you see we have Orders data for Year 2017,2019 and 2022. Now the business requirement is that we need data only for current year. So how you can get that...

SOLUTION

SELECT * FROM Orders_p
WHERE 
OrderDate >= TRUNC(SYSDATE) AND OrderDate <= ADD_MONTHS(TRUNC (SYSDATE ,'YEAR'),12)-1

Open in new window

See this portion -> OrderDate >= TRUNC(SYSDATE) AND OrderDate <= ADD_MONTHS(TRUNC (SYSDATE ,'YEAR'),12)-1

This portion  TRUNC(SYSDATE) will give me -> 01.01.2017 00:00:00
and this portion (ADD_MONTHS(TRUNC (SYSDATE ,'YEAR'),12)-1) will give me -> 31.12.2017 00:00:00

So we can now compare our ORDER DATES WITH THESE ....So these function will help us in DATE MANIPULATIONS in queries on the fly. These are ready made so we do not have to write out custom logic.

OUTPUT

--
| ORDERID |             ORDERDATE |
|---------|-----------------------|
|       1 | 2017-12-08 04:52:36.0 |
|       2 | 2017-12-08 04:52:36.0 |
|       5 | 2017-12-08 04:52:36.0 |
|       9 | 2017-12-08 04:52:36.0 |
--

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Pawan Kumar has provided a very good description of the use of trunc with dates. The real-life example is a good one, but with a little modification -
>>This portion  TRUNC(SYSDATE) will give me -> 01.01.2017 00:00:00<< needs to be
This portion  TRUNC(SYSDATE,'YEAR') will give me -> 01.01.2017 00:00:00
>>and OrderDate <= ADD_MONTHS(TRUNC (SYSDATE ,'YEAR'),12)-1 will give me 31.12.2017 00:00:00<<
should be -
 and orderDate < ADD_MONTHS(TRUNC (SYSDATE ,'YEAR'),12) (which will give me -> 1.1.2018 00:00:00)
so that an order made on 31.12.2017 at 5:00 pm will be included. Note the usage of less than ("<") rather than less than or equal to ("<=") so any orders up to 31.12.2017 11:59:59 pm would be included.
0
Pawan KumarDatabase ExpertCommented:
Thanks awking00... :)

Yes the where clause should be like below-

OrderDate >= TRUNC(SYSDATE) AND OrderDate < ADD_MONTHS(TRUNC (SYSDATE ,'YEAR'),12)

We should always use like >= AND < 
0
awking00Information Technology SpecialistCommented:
Where clause should still be like
OrderDate >= TRUNC(SYSDATE,'YEAR')
0
Pawan KumarDatabase ExpertCommented:
No in my example we need data for year 2017 only.
0
awking00Information Technology SpecialistCommented:
using today's date, TRUNC(SYSDATE) would give 8.12.2017 00:00:00 and TRUNC(SYSDATE,'YEAR') would give 1.1.2017 00:00:00 which is what I think you would want the orderdate to be greater than.
0
sukhoi35Author Commented:
Thank you Pawan, Paul and awking for helping me understand the concepts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.