How to achieve DB2 timestamp format through SAS?

[Krisan]

Hi every1,

I want to load the current system date and time from SAS to a field (specified in Timestamp format) in DB2. I tried using datetime format for that variable in SAS and using proc append to load data to db2 table. But i am getting format mismatch error. I cannot change the datatype of the field in DB2 as it is already existing table in PROD. Does anyone have an idea of how to achieve this?

Regards,

Krisan

[shivas]

Hi,

Just try these options and check..

SASDATEFMT or DBSASTYPE...

Thanks,

Shiva

[Linlin]

Hi Shiva,

Have not seen you on the forum for a while. Welcome back!

[ArtC]

You might also consider building a picture format.  For a datetime value you could use something like the following:

proc format;

picture dbdate

   other = '%Y-%0m-%0d:%0H:%0M:%0S' (datatype=datetime);

   run;

The %Y, %m, etc. are directives that can be used with picture formats.  There are over a dozen of them and they can be very helpful when working with date, time, and datetime values.

[jthy]

Old fashioned string handling?

data _null_;

  myDttm = datetime();

    db2ts = cats(put(datepart(myDttm), yymmdd10.), "-", tranwrd(put(timepart(myDttm), tod9.), ":", "."), ".", substr(put(timepart(myDttm), mmss15.6), 10, 6));

  put db2ts;

run;

2014-05-08-11.30.12.012000

[jakarman]

As you are appending the data the existing structure and the dataset you are building should be the same.

Your error-message is telling you something has failed with that. Try to find the reason by comparing those structures.