Home » SQL & PL/SQL » SQL & PL/SQL » Interval Clause in Date (Oracle11g)
Interval Clause in Date [message #668449] Mon, 26 February 2018 03:02 Go to next message
ssyr
Messages: 50
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 Go to previous messageGo to next message
John Watson
Messages: 7671
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 Go to previous message
cookiemonster
Messages: 13340
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.
Previous Topic: Character set Conversion
Next Topic: string format
Goto Forum:
  


Current Time: Mon Nov 19 11:26:30 CST 2018