how to define date of D-MMM-YY in a schema file

dsnovice · Post by **dsnovice** » Tue Dec 01, 2009 2:25 pm

Hello All,

I have a problem where the date that we get from a source file is in format of d-mmm-yy or .. dd-mmm-yyyy format.
like 1-Jan-09 the same row also contains 11-Jan-09.

So how do I define the record as a date in a schema file? I am planning to use Run time column propagation from there on to get it loaded into a table. Because of this req, handling the date by the field length in the transformer is not an option.

The table currently has the target columns as date. So when I run the job I get the error :
Conversion error calling conversion routine date_from_string data may have been lost

Thank you,

a novice

ray.wurlod · Post by **ray.wurlod** » Tue Dec 01, 2009 4:25 pm

You don't. You can define it as string, then bring it into a standard format (for example by adding leading zeroes to day and month where required) before converting to Date data type.

dsnovice · Post by **dsnovice** » Tue Dec 01, 2009 4:47 pm

Ray, Thank you for the reply.

Do you mean to say that all this could be done dynamically by using RCP or do I have to individually prefix the zero for each column where it is required?

What about tagged records? could that be employed to get around this? Kind of new to PX.. and curious.

thank you,

a novice

ray.wurlod · Post by **ray.wurlod** » Tue Dec 01, 2009 5:13 pm

That's not what RCP does, so no to that question. There is no automatic mechanism of which I am aware; put simply your data do not match your metadata (record schema) in all cases. Only one format can be specified for any single date field in a record schema.

dsnovice · Post by **dsnovice** » Tue Dec 01, 2009 5:24 pm

Thank you, That settles the case. I will load them as varchar into the tables by switching the date fields to Varchar(10).

Thank you,

a novice