Date format change

[karthi_gana]

All,

I have the below data in a file.

FM001 093011 0.233

I have to store this value in a datetime field column in sql server.


My job design is as below:

SeqFile --> Transformer --> ODBC

SeqFile:
ret_dt VARCHAR(10)

ODBC:
ret_dt TIMESTAMP 23 3

I tried the below codes:

Code: Select all

load_sec_returns.ret_dt[5,6]:'-':load_sec_returns.ret_dt[3,4]:'-':load_sec_returns.ret_dt[1,2]:' 00:00:000'

got the below error message

APT_CombinedOperatorController,1: Data string '11-31-08 00:00:000' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.

Code: Select all

SV = load_sec_returns.ret_dt[1,2]:'/':load_sec_returns.ret_dt[3,2]:'/':load_sec_returns.ret_dt[5,2]

OCONV(ICONV(StageVar,'D2/'),'D-E')

When i move the cursor outside the text box, i just end up with RED color. It means i am missing something.

Can you help me to resolve this issue?

[BI-RMA]

APT_CombinedOperatorController,1: Data string '11-31-08 00:00:000' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.

The message seems to me to be pretty straight-forward. You have got a date-value with a two-character year value and DataStage expects your timestamp to be in this format: '%yyyy-%mm-%dd %hh:%nn:%ss'.

You will have to be more specific as to how DataStage has to interpret your input-string. Use StringToTimestamp() in PX-Transformer.

Can't see how you want to use OConv/IConv in a parallel-environment (BasicTransformer?). The Parallel-Transformer does not know these functions (hence the RED color).

[manoj_23sakthi]

Hi ,
Your code looks good but you are using string function to time stamp
Apply your code in stage variable to extract the req out put
Stage var:
load_sec_returns.ret_dt[5,6]:'-':load_sec_returns.ret_dt[3,4]:'-':load_sec_returns.ret_dt[1,2]:' 00:00:000'

Use convert function :
StringToTimestamp(StageVar1,'%yyyy-%mm-%dd %hh:%nn:%ss')

I hope it may work
Manoj kumar

[BI-RMA]

By the way: The usage of substrings in Your code seems strange to me:

Columnname[x,y] means substring of Columnname from position x for y characters (not from position 5 to position 6!).

[karthi_gana]

SV1 = load_sec_returns.ret_dt[5,2]:'-':load_sec_returns.ret_dt[3,2]:'-':load_sec_returns.ret_dt[1,2]:' 00:00:000'

StringToTimestamp(SV1,'%yyyy-%mm-%dd %hh:%nn:%ss')


err msg:

APT_CombinedOperatorController,0: Data string '30-09-11' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': the value for tag %yyyy has fewer characters than expected.

[BI-RMA]

Year component still consists of two characters but your format specification says it has got a four character year component.

I can't see anything unclear in this message. Nobody can do the part of thinking for You.

[suse_dk]

The year format for your input is not equal to the format %yyyy

[karthi_gana]

yes. i would like to handle 2 digit year only in my job.

[karthi_gana]

yes. i would like to handle 2 digit year only in my job.

[BI-RMA]

StringToTimestamp(SV1,'%yyyy-%mm-%dd %hh:%nn:%ss')

Then You will have to tell DataStage what to do with Your string:

try StringToTimestamp(SV1,'%yy-%mm-%dd %hh:%nn:%ss')