topic Re: Load/Insert Timestamp field in QlikView

Load/Insert Timestamp field

Mon, 01 Sep 2014 15:03:02 GMT

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

Re: Load/Insert Timestamp field

marcus_sommer — Mon, 01 Sep 2014 15:39:20 GMT

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

MarcoWedel — Mon, 01 Sep 2014 18:38:57 GMT

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

stevedark — Mon, 01 Sep 2014 20:36:20 GMT

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

Tue, 02 Sep 2014 06:16:55 GMT

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

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

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

SQL SELECT

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