4 Replies Latest reply: Sep 2, 2014 2:16 AM by Gyanaranjan Tripathy RSS

    Load/Insert Timestamp field

    Gyanaranjan Tripathy

      Hi All,

      I'm trying to insert a Timestamp field.Not sure why it's throwing error.

       

      LET vTimeStamp = DATE(now(), 'YYYY/MM/DD hh:mm:ss TT');

      Table1:

      Load *;

      SQL SELECT

      .

      .

       

      TO_DATE('$(vTimeStamp)','YYYY/MM/DD HH:MI:SS AM') AS SNAP_TIMESTAMP

      From TABLE;

       

      This is DB2 database.

       

      even I tried to change the format to ('YYYY/MM/DD HH:MM:SS AM' or 'YYYY-MM-DD HH:MM:SS', the error still creeps in.

       

      Please suggest.

       

      Thanks in advance. stevedark

        • Re: Load/Insert Timestamp field
          Marcus Sommer

          You have set the variable in single-quotes which means it will be interpret as string. Also now() will be enough if you only want the timestamp with default-settings.

           

          By the way I would in large data/apps create a date- and a time-field then timestamps require often enormous amounts of ressources.

           

          - Marcus

          • Re: Load/Insert Timestamp field
            Marco Wedel

            Don't create the field in the DB but in QV using preceding load:

             

            Table1:

            Load *,

                      Timestamp(now(), 'YYYY/MM/DD hh:mm:ss TT') as SNAP_TIMESTAMP;

            SQL SELECT

            .

            .

            From TABLE;

            • Re: Load/Insert Timestamp field
              Steve Dark

              Hi there,

               

              I'm not too sure what it is you are trying to achieve here.  If you want a table with a single row and a single column of a date/time field you can just run this:

               

              LOAD

                   DATE(now(), 'YYYY/MM/DD hh:mm:ss TT') as SNAP_TIMESTAMP

              AUTOGENERATE(1);

               

              If you want the timestamp on every row of the table coming out of the database (Oracle?) I would suggest that you use the date function within the database.  In SQL Server this is GETDATE() or in Oracle I believe it is SYSDATE.

               

              As for why the code is causing an error I would suspect it is to do with the twelve hour clock and AM/PM being used, confusing the format.

               

              Steve

              • Re: Load/Insert Timestamp field
                Gyanaranjan Tripathy

                Hi,

                Thanks all for the reply.

                My bad. I don't want one field in my table first of all. Below is the modified script.

                 

                stevedark it's DB2 database. AS400

                 

                Inventory:

                Load*;

                SQL SELECT

                     A,

                     B,

                     C,

                TO_DATE('$(vTimeStamp)','YYYY/MM/DD HH:MI:SS AM') AS SNAP_TIMESTAMP

                From TABLE;

                 

                I'm gonna use this SNAP_TIMESTAMP field later for incremental loading.

                If i use preceding load to achieve this SNAP_TIMESTAMP like

                 

                Inventory:

                Load

                     A,

                     B,

                     C,

                     DATE(now(), 'YYYY/MM/DD hh:mm:ss TT') as SNAP_TIMESTAMP;

                SQL SELECT

                     A,

                     B,

                     C

                From TABLE;

                It's working fine though, i wanted to use a variable vTimestamp instead of now() as i'll store a particular date in vTimestamp.


                Thanks again..