DB2 date format

ray.wurlod · Post by **ray.wurlod** » Wed Jun 27, 2007 3:29 pm

What is the value coming in from DSLink8.PROCESS_DT ?

Capture this using stage tracing or the Debugger or an extra output from the Transformer stage to a text file.

naren6876 · Post by **naren6876** » Wed Jun 27, 2007 4:26 pm

ray.wurlod wrote:What is the value coming in from DSLink8.PROCESS_DT ?

Capture this using stage tracing or the Debugger or an extra output from the Transformer stage to a text file. ...

The value coming in that link is 20070627

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Wed Jun 27, 2007 8:27 pm

OK, now output FMT(DSLink8.PROCESS_DT, "L####/##/##") to establish whether the problem is in Fmt() or Iconv().

rafik2k · Post by **rafik2k** » Thu Jun 28, 2007 3:28 am

What is the source datatype? and what is the target db2 date format?

Depending upon your target format, use iconv and oconv function accordingly like

Code: Select all

Oconv((Iconv(DSLink8.PROCESS_DT,"DYMD")),"D-YMD[4,2,2]")

ray.wurlod · Post by **ray.wurlod** » Thu Jun 28, 2007 7:08 am

Can you please also output Len(Fmt(DSLink8.PROCESS_DT, "L####/##/##")) and Quote(Fmt(DSLink8.PROCESS_DT, "L####/##/##")) so we can be sure there are no non-printing characters?

Although theoretically unnecessary, try being specific about the format in the Iconv() function.
Iconv(Fmt(DSLink8.PROCESS_DT, "L####/##/##"), "D/YMD[4,2,2]")
or
Iconv(Left(Fmt(DSLink8.PROCESS_DT, "L####/##/##"), 10), "DYMD")

naren6876 · Post by **naren6876** » Thu Jun 28, 2007 7:22 am

ray.wurlod wrote:Can you please also output Len(Fmt(DSLink8.PROCESS_DT, "L####/##/##")) and Quote(Fmt(DSLink8.PROCESS_DT, "L####/##/##")) so we can be sure there are no non-printing characters?

Although theoretic ...

The output of
Len(Fmt(DSLink8.PROCESS_DT, "L####/##/##")) = 10

Quote(Fmt(DSLink8.PROCESS_DT, "L####/##/##"))
and I am not able to use the Quote and receiving message like

variable 'Quote' not defined.

Thanks in advance.

ArndW · Post by **ArndW** » Thu Jun 28, 2007 6:56 pm

naren6876, add a parenthesis, the line is

Code: Select all

Quote(Fmt(DSLink8.PROCESS_DT, "L####/##/##")))

chulett · Post by **chulett** » Thu Jun 28, 2007 10:09 pm

Two lefts and three rights, Arnd?

DQuote or SQuote, there is no Quote.

ArndW · Post by **ArndW** » Thu Jun 28, 2007 10:13 pm

Craig - I guess I really can't count. Silly me.

Naren6876 - Please ignore my earlier post and follow Master Craig's suggestion.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 28, 2007 10:23 pm

There IS a Quote function - I use it a lot.

Quote is not in DSParams, but you can put it there so that it should compile and run successfully. But DQuote or SQuote will serve as well for the current purpose.

chulett · Post by **chulett** » Fri Jun 29, 2007 6:24 am

There is? It didn't come up in the ellipsis list of functions and the OP's error suggested it didn't exist. The online help has an entry for Quote but actually brings up DQuote.

I'll take your word for it.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 29, 2007 6:34 am

As noted, 'tis not in DSParams.

chulett · Post by **chulett** » Fri Jun 29, 2007 6:35 am

As noted, 'tis not in the Help either.