Interval Clause in Date [message #668449] |
Mon, 26 February 2018 03:02 |
|
ssyr
Messages: 65 Registered: January 2017
|
Member |
|
|
Hi all,
I am working on query it its using date fields column to generate the records. But it has Interval '1' seconds clause.
I am able to understand use of this clause? Can anyone please help me on this use of Interval clause.
I have tried below 2 queries but both generates different output ?
select trunc(trunc(sysdate,'month')-1,'month') , trunc(sysdate,'month')-interval '1' second from dual;
O/P :01-JAN-18 31-JAN-18
select add_months(trunc(sysdate,'mm'),-1), last_day(add_months(trunc(sysdate,'mm'),-1))-interval '1' second from dual;
o/p: 01-JAN-18 30-JAN-18
I have to generate data for full last month ?How Can I get this?
Please suggest on this.
Thank you in advance.
|
|
|
Re: Interval Clause in Date [message #668450 is a reply to message #668449] |
Mon, 26 February 2018 03:14 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You need to develop your problem solving skills. In this case, you say that you don't understand what the INTERVAL clause is doing. So work it out. Build up to it slowly. In this case:orclx>
orclx> alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss';
Session altered.
orclx> select sysdate from dual;
SYSDATE
-------------------
2018-02-26:09:12:38
orclx> select trunc(sysdate) from dual;
TRUNC(SYSDATE)
-------------------
2018-02-26:00:00:00
orclx> select trunc(sysdate,'mm') from dual;
TRUNC(SYSDATE,'MM')
-------------------
2018-02-01:00:00:00
orclx> select add_months(trunc(sysdate,'mm'),-1) from dual;
ADD_MONTHS(TRUNC(SY
-------------------
2018-01-01:00:00:00
orclx> select last_day(add_months(trunc(sysdate,'mm'),-1)) from dual;
LAST_DAY(ADD_MONTHS
-------------------
2018-01-31:00:00:00
orclx> select last_day(add_months(trunc(sysdate,'mm'),-1)) - interval '1' second from dual;
LAST_DAY(ADD_MONTHS
-------------------
2018-01-30:23:59:59
orclx> all clear now?
|
|
|
Re: Interval Clause in Date [message #668458 is a reply to message #668450] |
Mon, 26 February 2018 04:10 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you want to get the full previous month then there's no need for interval:
date_col >= trunc(add_months(sysdate,-1), 'MM')
and date_col < trunc(sysdate, 'MM')
If you're using between (which does a >= and <=) then it's
col between midnight on 1st of month and 23:59:59 on last day of month.
But
<= 23:59:59 on last day of month
is equivalent to
< midnight on first day of following month
Which is easier to calculate.
|
|
|