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.
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:
DATE(now(), 'YYYY/MM/DD hh:mm:ss TT') as SNAP_TIMESTAMP
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.
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
TO_DATE('$(vTimeStamp)','YYYY/MM/DD HH:MI:SS AM') AS SNAP_TIMESTAMP
I'm gonna use this SNAP_TIMESTAMP field later for incremental loading.
If i use preceding load to achieve this SNAP_TIMESTAMP like
DATE(now(), 'YYYY/MM/DD hh:mm:ss TT') as SNAP_TIMESTAMP;
It's working fine though, i wanted to use a variable vTimestamp instead of now() as i'll store a particular date in vTimestamp.