This discussion is archived
7 Replies Latest reply: May 25, 2010 9:32 AM by 775133 RSS

DateADD in oracle - How to add the date

520411 Newbie
Currently Being Moderated
Hi All,
I have the table which has startdttm (date )as one of the columns in table, myTable and I want to substract 2 months from this date of the column startdttm and set it back to startdttm. For example the startdttm has '2007-04-01 00:00:00'
and I want to change it to '2007-02-01 00:00:00'. In MSSQL has dateAdd function but not in ORACLE. So do you know any similar function to add the date.
Thanks,
JP
  • 1. Re: DateADD in oracle - How to add the date
    Paul M. Oracle ACE
    Currently Being Moderated
    SQL> create table t01(a date);

    Table created.

    SQL> insert into t01 values(to_date('2007-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));

    1 row created.

    SQL> select * from t01;

    A
    ---------
    01-APR-07

    SQL> update t01 set a = add_months(a, -2);

    1 row updated.

    SQL> select * from t01;

    A
    ---------
    01-FEB-07

    SQL> select to_char(a,'yyyy-mm-dd hh24:mi:ss') from t01;

    TO_CHAR(A,'YYYY-MM-
    -------------------
    2007-02-01 00:00:00

    SQL>
  • 2. Re: DateADD in oracle - How to add the date
    520411 Newbie
    Currently Being Moderated
    It worked!! Thank a lot.
    JP
  • 3. Re: DateADD in oracle - How to add the date
    520411 Newbie
    Currently Being Moderated
    How about for hourly, daily, weekly?
    I could not find the function call add_day ...
    Please help if you know what is the function for adding hourly, daily and weekly
    Thanks,
    JP
  • 4. Re: DateADD in oracle - How to add the date
    Paul M. Oracle ACE
    Currently Being Moderated
    You can use INTERVAL expressions :
    SQL> select to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'),
      2  to_char(sysdate - interval '2' HOUR,'dd/mm/yyyy hh24:mi:ss')
      3  from dual;

    TO_CHAR(SYSDATE,'DD TO_CHAR(SYSDATE-INT
    ------------------- -------------------
    30/05/2007 16:27:06 30/05/2007 14:27:06

    SQL> select sysdate, sysdate - interval '2' DAY from dual;

    SYSDATE   SYSDATE-I
    --------- ---------
    30-MAY-07 28-MAY-07

    SQL>
  • 5. Re: DateADD in oracle - How to add the date
    476429 Newbie
    Currently Being Moderated
    Hi Paul,
    Nice one.

    Thanks.
  • 6. Re: DateADD in oracle - How to add the date
    775133 Newbie
    Currently Being Moderated
    Hi Paul,

    select * from bmc.vw_summarydata
    where to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'),
    to_char(sysdate - interval '1' HOUR,'dd/mm/yyyy hh24:mi:ss')
    and parameter in
    ('UserConnections','DiskIoErrrs','DiskIoReads','DiskIoWrites','PacketErrors','CacheHitRatio','CpuIdle','CpuIoBusy','TransactionsPerSec','FullScansPerSec')
    and appclass like 'MSSQL%'
    order by AGENT

    and i am trying to find the data for the last 24 hours let say i want data foir 24th May from 00 hours to 24th may 23:00 hours how can we do that.

    I will also give you the brief of size of the table is 50 GB and the below are the structure of the table

    COLUMN_NAME     DATA_TYPE
    AGENT     NVARCHAR2(2214)
    APPCLASS     NVARCHAR2(2048)
    INSTANCE     NVARCHAR2(4000)
    PARAMETER     NVARCHAR2(2048)
    GMTTIMESTAMPSTR     VARCHAR2(31)
    VALUECOUNT     NUMBER(38,0)
    VALUEMIN     FLOAT
    VALUEMAX     FLOAT
    VALUESUM     FLOAT
    VALUEAVG     NUMBER
    GMTTIMESTAMP     DATE


    If i use the GMTTIMESTAMP for a specific date i am able to extract the data, however if i want to do it automatically by the script without manual intervention of me entering the date how to achieve it. I am using this as source script to select the data. Then i load it on the SQL Database for which i have the query. However the source is from oracle thats where the issue. I did found out that there is no Dateadd function in oracle.


    This what is want to achieve as did the same for sysbase. I was struck in the DATEADD function

    where DateAdd (ss, timestamp, '1/1/70') in GMTTIMESTAMP
    and DateAdd( ss, timestamp, '1/1/70') > DateAdd (dd,-1,substr(convert(to_char(12),'getdate()', 0), 1, 12) + '12:00AM')
    and DateAdd( ss, timestamp, '1/1/70') < substr(convert(to_char(12),'getdate()', (0), 1, 12) + '12:00AM')

    Kindly help me please.
  • 7. Re: DateADD in oracle - How to add the date
    775133 Newbie
    Currently Being Moderated
    Hi Paul,

    select * from bmc.vw_summarydata
    where to_char(sysdate,'dd/mm/yyyy hh24:mi:ss'),
    to_char(sysdate - interval '1' HOUR,'dd/mm/yyyy hh24:mi:ss')
    and parameter in
    ('UserConnections','DiskIoErrrs','DiskIoReads','DiskIoWrites','PacketErrors','CacheHitRatio','CpuIdle','CpuIoBusy','TransactionsPerSec','FullScansPerSec')
    and appclass like 'MSSQL%'
    order by AGENT

    and i am trying to find the data for the last 24 hours let say i want data foir 24th May from 00 hours to 24th may 23:00 hours how can we do that.

    I will also give you the brief of size of the table is 50 GB and the below are the structure of the table

    COLUMN_NAME     DATA_TYPE
    AGENT     NVARCHAR2(2214)
    APPCLASS     NVARCHAR2(2048)
    INSTANCE     NVARCHAR2(4000)
    PARAMETER     NVARCHAR2(2048)
    GMTTIMESTAMPSTR     VARCHAR2(31)
    VALUECOUNT     NUMBER(38,0)
    VALUEMIN     FLOAT
    VALUEMAX     FLOAT
    VALUESUM     FLOAT
    VALUEAVG     NUMBER
    GMTTIMESTAMP     DATE


    If i use the GMTTIMESTAMP for a specific date i am able to extract the data, however if i want to do it automatically by the script without manual intervention of me entering the date how to achieve it. I am using this as source script to select the data. Then i load it on the SQL Database for which i have the query. However the source is from oracle thats where the issue. I did found out that there is no Dateadd function in oracle.


    This what is want to achieve as did the same for sysbase. I was struck in the DATEADD function

    where DateAdd (ss, timestamp, '1/1/70') in GMTTIMESTAMP
    and DateAdd( ss, timestamp, '1/1/70') > DateAdd (dd,-1,substr(convert(to_char(12),'getdate()', 0), 1, 12) + '12:00AM')
    and DateAdd( ss, timestamp, '1/1/70') < substr(convert(to_char(12),'getdate()', (0), 1, 12) + '12:00AM')

    Kindly help me please.