Load/Insert Timestamp field

[#1]

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. Steve Dark

[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

[MarcoWedel]

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;

[stevedark]

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

[#5]

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.

Steve Dark 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..