VarChar to Date Problem

bobby28 · Post by **bobby28** » Wed Aug 09, 2006 2:02 pm

I am trying to pull from a sequential file to a DB2 table (DB2 Enterprise Stage):

1. File has char and date fields and "View Data" option works fine as long as I use the column format as VarChar. Most os the dates are NULL.

2. I am using StringToDate function for converting the VarChar Date values to DATE but getting an error: " Invalid Format [YYYY-MM-DD] used for string_from_date type conversion"

Can you help me with this conversion problem.

kris007 · Post by **kris007** » Wed Aug 09, 2006 3:11 pm

Can you post an example of how your input Date looks like and the derivation using StringtoDate type conversion?

bobby28 · Post by **bobby28** » Wed Aug 09, 2006 4:01 pm

kris007 wrote:Can you post an example of how your input Date looks like and the derivation using StringtoDate type conversion?

The Date from souce file looks like: -2006-01-01 and is defined as VarChar.

On the insert I am using Enterprise stage on DB2 database which accepts the format as 2006-01-01.

Thanks for your reply. Please let me know...

kris007 · Post by **kris007** » Wed Aug 09, 2006 4:09 pm

Does the date contain "-"character at the beginning? If so, are you ignoring it?Can you verify if your derivation for this Date column in the Transformer Stage looks like this

Code: Select all

StringToDate(InputLink.ColumnName,"%yyyy-%mm-%dd")

and you have set the Datatype in the output of the Transformer as Date for this Column?
Since you say that most of the rows are nulls, are you handling nulls?

madhukar · Post by **madhukar** » Thu Aug 10, 2006 4:25 am

[quote]

[code]StringToDate(InputLink.ColumnName,"%yyyy-%mm-%dd") [/code] and you have set the Datatype in the output of the Transformer as Date for this Column?
Since you say that most of the rows are nulls, are you handling nulls?[/quote]

i thk it should be

StringToDate(InputLink.ColumnName[2,11],"%yyyy-%mm-%dd")

bobby28 · Post by **bobby28** » Thu Aug 10, 2006 3:09 pm

madhukar wrote:
Code: Select all
StringToDate(InputLink.ColumnName,"%yyyy-%mm-%dd") 
and you have set the Datatype in the output of the Transformer as Date for this Column?
Since you say that most of the rows are nulls, are you handling nulls?
i thk it should be

StringToDate(InputLink.ColumnName[2,11],"%yyyy-%mm-%dd")

Thank you, I tried both options and it worked for me.