DB2 datetime format

[SujathaBalachandran]

Hi,

I'm trying to generate an xl report from DB2 table using SAS. Could you please help me achieve the timestamp field in the DB2 format eg: 2014-10-24-10:44:25:56473.

I tried using the PICTURE FORMAT as below. But this formats up to Seconds. The fraction part of the seconds are not getting generated in the report.

proc format;

picture db2date

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

   run;

Please let me know your ideas to resolve this issue.

Sujatha

[Kurt_Bremser]

newval = put(datepart(oldval),yymmddd10.)!!'-'!!put(timepart(oldval),time15.6);

You may need to take care of blanks for hours<10

fixed "d" for dash/hyphen

[SujathaBalachandran]

Thanks a lot!

I tried this and it worked perfect!

newval = put(datepart(oldval),yymmddd10.)!!'-'!!put(timepart(oldval),tod15.6);

Sujatha

[jakarman]

When you have SAS/Access Db2 there is an automatic conversion done.  SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

Is this what your question is about?

Within DB2 there are date/time functions and an automatic conversion is done to some internal format. DB2 Basics: Fun with Dates and Times

Or is it this area.

Dates/Tiems got an ISO standard 8601, that includes a l lot. SAS(R) 9.4 Formats and Informats: Reference
   

[Patrick]

If you replace the '%0S' directive with '%0s' then things should work (so: lower case instead of upper case 's').

Base SAS(R) 9.4 Procedures Guide, Third Edition

proc format;

  picture db2date

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

run;

data have;

  format dttm_val best32. dttm_pict db2date26.5;

  dttm_val=input('24Oct2014 10:44:25.56473',datetime26.5);

  dttm_pict=dttm_val;

run;

proc print data=have;

run;

[BrunoMueller]

Hi You can build your own format using Proc Format, see an example below:

proc format;
  picture db2date (default=26)
    other = '%Y-%0m-%0d:%0H:%0M:%0s' (datatype=datetime)
  ;
run;

data _null_;
  now = datetime();
  now2 = now;
  format now db2date.6 now2 datetime25.6;
  putlog (now now2) (=/);
run;

[SujathaBalachandran]

Thank you for the suggestion!.

I tried the below coding

proc format;
  picture db2date (default=26)
    other = '%Y-%0m-%0d:%0H:%0M:%0s' (datatype=datetime)
  ;
run;

proc sql;

create mySasTbl as

  select timstmp1

   from db2table1

where cond1 = true;

run;

proc print data=mySasTbl  label noobs;

format  timstmp1  db2date26.;   -> db2date26. generates report as 2014-10-24-10:44:

run;

format myTblField db2date6.;   -> db2date6. generates report as 2014-1

any suggestion is of great help!

Thanks!

Sujatha

[Kurt_Bremser]

Since the picture format directive for seconds (%s) displays only 2 digits for seconds, and one cannot combine the directives with .9999 for digits after the comma, you may need to employ my previously posted method.

[BrunoMueller]

You must use the format as seen in my example, like

format now db2date.6 now2 datetime25.6;

It is important to specify the decimals, the number after the period

[Kurt_Bremser]

Does not work, at least in 9.2

(the puts from the log, using exactly your code)

now=2014-10-28:07:16:

now2=28OCT2014:07:16:55.128662

In which environment did you test it?

[BrunoMueller]

I used SAS9.4M2, where this works just fine.

See also Base SAS(R) 9.3 Procedures Guide, Second Edition where it shows how to use a user written function to format values. But only with SAS9.3

[Kurt_Bremser]

OK, then there is an obvious difference in how SAS handles the picture directives between 9.2 and 9.3/9.4

[jakarman]

47182 - Adding a leading zero to the %s directive in a PICTURE statement in PROC FORMAT does not ins...

take care of differences at releases and notes.