Page 1 of 1
Corvertiion > Timestamp to YYYY-MM-DD-HH:MM:SS.ssssss for
Posted: Sat Jan 26, 2013 9:31 pm
by 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
Posted: Sun Jan 27, 2013 3:20 am
by ray.wurlod
What is the format of the timestamp returned by DB2?
Your expression seems fine to me.
Posted: Sun Jan 27, 2013 6:22 am
by 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.
Posted: Sun Jan 27, 2013 8:04 am
by 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"?
Posted: Sun Jan 27, 2013 8:29 am
by cosec
It's TIMESTAMP in db2.
The value in the db is 2012-01-15 01:15:14.3
Posted: Sun Jan 27, 2013 5:25 pm
by 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.