Home » SQL & PL/SQL » SQL & PL/SQL » subtract date (SQL,9.2.0.1.0,XP)
subtract date [message #400449] Tue, 28 April 2009 04:40 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

is there any oracle function through that i can first subtract date 24 months and then change it into first day of the month,please see details


SQL> create table test
  2  (inv_dt date);

Table created.

insert into test
    (inv_dt)
    values
    ('13JAN08');
insert into test
    (inv_dt)
    values
   ('13JAN07');

insert into test
   (inv_dt)
    values
   ('12JAN06');

insert into test
    (inv_dt)
    values
   ('31MAY09');


insert into test
    (inv_dt)
    values
    ('30APR07');
insert into test
    (inv_dt)
    values
   ('01APR07');
insert into test
    (inv_dt)
    values
   ('02APR07');

SQL> SELECT * FROM TEST;

INV_DT
---------
13-JAN-08
13-JAN-07
12-JAN-06
31-MAR-09
30-APR-07
01-APR-07
02-APR-07

I am using the following query
SELECT INV_DT
--TO_CHAR(ADD_MONTHS('31MAR09',-23))
FROM TEST
WHERE INV_DT BETWEEN TO_CHAR(ADD_MONTHS('31MAR09',-23))
AND '31MAR09'

the system display the following output.

INV_DT
---------
13-JAN-08
31-MAR-09
30-APR-07

means system display the date from 30APR07 to 31mar09 because of 31MAr09 input but i want the range 01April07 instead of 30Apr07 ,how can i change 30Apr07 to 01Apr07 without change the 31mar09,kindly suggest
my required output is

01-APR-07
02-APR-07
13-JAN-08
31-MAR-09
30-APR-07




by using the above query the system gives the range from 30April07 to 31mar09 but i want to convert 30Apr07range to


Re: subtract date [message #400450 is a reply to message #400449] Tue, 28 April 2009 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there any oracle function through that i can first subtract date 24 months and then change it into first day of the month

ADD_MONTHS
TRUNC

Regards
Michel

[Updated on: Tue, 28 April 2009 04:44]

Report message to a moderator

Re: subtract date [message #400717 is a reply to message #400449] Wed, 29 April 2009 06:19 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks,my problem is solved as per your advised.
Previous Topic: IO cost is high
Next Topic: Difference between
Goto Forum:
  


Current Time: Sun Dec 11 02:24:14 CST 2016

Total time taken to generate the page: 0.08635 seconds