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.
VarChar to Date Problem
Moderators: chulett, rschirm, roy
The Date from souce file looks like: -2006-01-01 and is defined as VarChar.kris007 wrote:Can you post an example of how your input Date looks like and the derivation using StringtoDate type conversion?
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...
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 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?
Code: Select all
StringToDate(InputLink.ColumnName,"%yyyy-%mm-%dd")
Since you say that most of the rows are nulls, are you handling nulls?
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
[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")
[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")
Thank you, I tried both options and it worked for me.madhukar wrote:i thk it should beand you have set the Datatype in the output of the Transformer as Date for this Column?Code: Select all
StringToDate(InputLink.ColumnName,"%yyyy-%mm-%dd")
Since you say that most of the rows are nulls, are you handling nulls?
StringToDate(InputLink.ColumnName[2,11],"%yyyy-%mm-%dd")