Corvertiion > Timestamp to YYYY-MM-DD-HH:MM:SS.ssssss for

[cosec]

(a) Source: DB2 UDB Database
(b) Target : Sequential file
(c) Objective: Timestamp value to be represented in YYYY-MM-DD-HH:MM:SS.ssssss format

[ray.wurlod]

What is the format of the timestamp returned by DB2?
Your expression seems fine to me.

[cosec]

If I don't use the substring function, the format received is as follows:

YYYY-MM-DD HH:MM:SS.ssssss (I need to have a '-' separator between the date and time).

Thanks.

[ArndW]

What datatype is Input.EFF_DTM that you are reading from DB2? Also, is the 6th decimal digit in DB2 actually set to a value other than "1"?

[cosec]

It's TIMESTAMP in db2.

The value in the db is 2012-01-15 01:15:14.3

[ray.wurlod]

OK, so you need to convert the space to a "-" and force six places in the fractional seconds piece. Try something like this:

Code: Select all

Convert(" ", "-", Field(InLink.TheTimestamp, ".", 1,1 ) : "." : Fmt(Field(InLink.TheTimestamp, ".", 2, 1), "L%6")

That is, everything to the left of the "." with the space character converted, concatenated with a dot and then everything to the right of the dot with sufficient trailing zeroes to make six characters.

Some people prefer the alternative formulation "6'0'L" rather than "L%6" for the format string argument of the Fmt() function. They are equivalent.