Problem in String to Date conversion

[jaysheel]

Hi Experts,

I am trying to convert an input column coming as varchar to Date format.
The input is coming in the format "mm/dd/yy 00:00:0"

I have used the combination of below functions in transformer.

StringToDate(Convert( "0:00:00","", LNK_XFRM_REAL_EST_STORES_DM.store_open_date) ,"%mm-%dd-%yyyy")

and I am getting the column as ******* in peek stage.

And when I am trying to load the database (Netezza) I am gettng the error

main_program: Fatal Error: Added field has duplicate identifier():


I have tried out all possible methods.. Can somebody help me out to get around this.. ?

Thanks in Advance.

[ArndW]

Code: Select all

StringToDate(LNK_XFRM_REAL_EST_STORES_DM.store_open_date[1,8] ,"%mm/%dd/%yyyy")

The important change is in the format mask for the date, the substring is more efficient and versatile than the original convert() function but functionality is the same for your data.

[jaysheel]

Hi,

Thanks for the response. I tried the below code and got same ****** in peek stage.

StringToDate(LNK_XFRM_REAL_EST_STORES_DM.store_open_date[1,8],"%mm/%dd/%yyyy")

Any idea whats wrong happening ?

[balajisr]

In your case, year is actually two characters long. It should be "yy" rather than "yyyy"

Code: Select all

StringToDate(LNK_XFRM_REAL_EST_STORES_DM.store_open_date[1,8],"%mm/%dd/%yy") 

[jaysheel]

Yes the source data is in the form 'yy'. But i want to convert it to 'yyyy'.
Thats why I have put as 'yyyy'. And I am removing the 00:00:0 part from the source using the function 'convert'. I am banging my head for this since past week. Anybody ever came across such a thing before ?

[ArndW]

The format needs to be that of the source, I should have caught the 4 digit error, sorry. You can then choose your display options for the date elsewhere to display 4 digits.

[balajisr]

StringToDate() will convert your source to date in the format yyyy-mm-dd automatically. Output it to peek stage to verify the output.