7 Replies Latest reply: May 25, 2010 11:32 AM by 775133 RSS

    DateADD in oracle - How to add the date

    520411
      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.
          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
            It worked!! Thank a lot.
            JP
            • 3. Re: DateADD in oracle - How to add the date
              520411
              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.
                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
                  Hi Paul,
                  Nice one.

                  Thanks.
                  • 6. Re: DateADD in oracle - How to add the date
                    775133
                    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
                      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.