8 Replies Latest reply on Jan 26, 2013 2:15 PM by Etbin

# Round and Trunc with dates

Can i get the examples for Round and Trunc with dates?
• ###### 1. Re: Round and Trunc with dates
Something to play with: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions255.htm#SQLRF52058

Regards

Etbin
• ###### 2. Re: Round and Trunc with dates
It would take you less time to read docs TRUNC(datetime).

SY.
• ###### 4. Re: Round and Trunc with dates
Hi Rahul,

Check out this workout by Tom Kyte on TRUNC and ROUND -
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52sql-1735910.html

Dont skip even a single line of it. Really useful.

My little workout :
``````Ranit>> select
2  round(123.444) r1,
3  round(123.999) r2,
4  trunc(123.444) t1,
5  trunc(123.999) t2
6  from
7  dual;

R1         R2         T1         T2
---------- ---------- ---------- ----------
123        124        123        123

Ranit>> select
2  round(123.444,2) r1,
3  round(123.999,2) r2,
4  trunc(123.444,2) t1,
5  trunc(123.999,2) t2
6  from dual;

R1         R2         T1         T2
---------- ---------- ---------- ----------
123.44        124     123.44     123.99

Ranit>> select
2  round(123.1234,2) r1, -- "Rounds upto 2 Decimal places"
3  round(123.1266,2) r2, -- "Rounds upto 2 Decimal places"
4  trunc(123.1234,2) t1, -- "Keeps only 2 decimal places and Truncates the rest"
5  trunc(123.1266,2) t2  -- "Keeps only 2 decimal places and Truncates the rest"
6  from
7  dual;

R1         R2         T1         T2
---------- ---------- ---------- ----------
123.12     123.13     123.12     123.12

--"Note the difference in R2 andT2 values : In R2 there's a data round-off done but in T2 it is simple cut-off of extra decimal part"``````
HTH

Edited by: ranit B on Jan 26, 2013 7:24 PM
• ###### 5. Re: Round and Trunc with dates
ranit B wrote:
Dont skip even a single line of it. Really useful.
Question was on ROUND/TRUNC date, not number.

SY.
• ###### 6. Re: Round and Trunc with dates
Solomon Yakobson wrote:
ranit B wrote:
Dont skip even a single line of it. Really useful.
Question was on ROUND/TRUNC date, not number.

SY.
i am aware how they work with numbers
I need example with dates.
• ###### 7. Re: Round and Trunc with dates
Ohh my mistake!

@Rahul - But did you try doing some experiment yourself. It is pretty much the same.
Please tell us how much you tried and what made you stop?

I tried -
``````Ranit>> select
2  to_char(round(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),'dd-mon-yyyy hh:mi:ss am'),
3  to_char(trunc(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),'dd-mon-yyyy hh:mi:ss am')
4  from dual;

TO_CHAR(ROUND(TO_DATE(' TO_CHAR(TRUNC(TO_DATE('
----------------------- -----------------------
13-jan-2012 12:00:00 am 12-jan-2012 12:00:00 am

Ranit>> select
2  round(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),
3  trunc(to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')),
4  to_date('12-jan-2012 2:2:2 pm','dd-mon-yyyy hh:mi:ss am')
5  from dual;

ROUND(TO_ TRUNC(TO_ TO_DATE('
--------- --------- ---------
13-JAN-12 12-JAN-12 12-JAN-12   ``````
Edited by: ranit B on Jan 26, 2013 7:38 PM
• ###### 8. Re: Round and Trunc with dates
Like this ? ;)
``````with
test_date as
(select to_date(:a_date,'yyyymmddhh24miss') the_date
from dual
)
select the_date,'CC' pattern,round(the_date,'CC') rounded,trunc(the_date,'CC') truncated from test date
union all
select the_date,'SCC' pattern,round(the_date,'SCC') rounded,trunc(the_date,'SCC') truncated from test date
union all
select the_date,'SYYYY' pattern,round(the_date,'SYYYY') rounded,trunc(the_date,'SYYYY') truncated from test date
union all
select the_date,'YYYY' pattern,round(the_date,'YYYY') rounded,trunc(the_date,'YYYY') truncated from test date
union all
select the_date,'YEAR' pattern,round(the_date,'YEAR') rounded,trunc(the_date,'YEAR') truncated from test date
union all
select the_date,'SYEAR' pattern,round(the_date,'SYEAR') rounded,trunc(the_date,'SYEAR') truncated from test date
union all
select the_date,'YYY' pattern,round(the_date,'YYY') rounded,trunc(the_date,'YYY') truncated from test date
union all
select the_date,'YY' pattern,round(the_date,'YY') rounded,trunc(the_date,'YY') truncated from test date
union all
select the_date,'Y' pattern,round(the_date,'Y') rounded,trunc(the_date,'Y') truncated from test date
union all
select the_date,'IYYY' pattern,round(the_date,'IYYY') rounded,trunc(the_date,'IYYY') truncated from test date
union all
select the_date,'IYY' pattern,round(the_date,'IYY') rounded,trunc(the_date,'IYY') truncated from test date
union all
select the_date,'IY' pattern,round(the_date,'IY') rounded,trunc(the_date,'IY') truncated from test date
union all
select the_date,'I' pattern,round(the_date,'I') rounded,trunc(the_date,'I') truncated from test date
union all
select the_date,'Q' pattern,round(the_date,'Q') rounded,trunc(the_date,'Q') truncated from test date
union all
select the_date,'MONTH' pattern,round(the_date,'MONTH') rounded,trunc(the_date,'MONTH') truncated from test date
union all
select the_date,'MON' pattern,round(the_date,'MON') rounded,trunc(the_date,'MON') truncated from test date
union all
select the_date,'MM' pattern,round(the_date,'MM') rounded,trunc(the_date,'MM') truncated from test date
union all
select the_date,'RM' pattern,round(the_date,'RM') rounded,trunc(the_date,'RM') truncated from test date
union all
select the_date,'WW' pattern,round(the_date,'WW') rounded,trunc(the_date,'WW') truncated from test date
union all
select the_date,'IW' pattern,round(the_date,'IW') rounded,trunc(the_date,'IW') truncated from test date
union all
select the_date,'W' pattern,round(the_date,'W') rounded,trunc(the_date,'W') truncated from test date
union all
select the_date,'DDD' pattern,round(the_date,'DDD') rounded,trunc(the_date,'DDD') truncated from test date
union all
select the_date,'DD' pattern,round(the_date,'DD') rounded,trunc(the_date,'DD') truncated from test date
union all
select the_date,'J' pattern,round(the_date,'J') rounded,trunc(the_date,'J') truncated from test date
union all
select the_date,'DAY' pattern,round(the_date,'DAY') rounded,trunc(the_date,'DAY') truncated from test date
union all
select the_date,'DY' pattern,round(the_date,'DY') rounded,trunc(the_date,'DY') truncated from test date
union all
select the_date,'D' pattern,round(the_date,'D') rounded,trunc(the_date,'D') truncated from test date
union all
select the_date,'HH' pattern,round(the_date,'HH') rounded,trunc(the_date,'HH') truncated from test date
union all
select the_date,'HH12' pattern,round(the_date,'HH12') rounded,trunc(the_date,'HH12') truncated from test date
union all
select the_date,'HH24' pattern,round(the_date,'HH24') rounded,trunc(the_date,'HH24') truncated from test date
union all
select the_date,'MI' pattern,round(the_date,'MI') rounded,trunc(the_date,'MI') truncated from test date``````
Regards

Etbin