StringToDate conversion problem

[mac4rfree85]

Hi Guys,

My Source is SAP which is having a Timestamp value in Char field. So,i am converting the Character field to Date using the below code

Code: Select all

StringToDate(Colname,"%yyyy-%mm%dd")

But i am getting a warning for all the rows and the target is not getting loaded. The warning is

Code: Select all

Conversion error calling conversion routine date_from_ustring data may have been lost

Can somebody guide as where i am doing a mistake.

Cheers!!!!!

[kbsuryadev]

StringToDate(Column,"%yyyy-%mm-%dd")

[chulett]

Show us what your timestamps look like.

[mac4rfree85]

@kbsuryadev,, ya correct.. it was a typo.. still showing the same error.
@chulett, my source data is like this 20101007063654

[jwiles]

You have two issues at work here:

1) Your format string does NOT match the format of your timestamp string (I do not see dashes in the source)
2) You're attempting to process a timestamp string with a date function and format.

With StringToDate() and StringToTimestamp(), the format string tells the function what all the various parts of the source string should be. If you have a dash/slash/period/etc. in the format string, it needs to be in your incoming data.

Are you trying to process a timestamp (date+time) or just a date? If the former, use StringToTimestamp() with the proper format string. If the latter, you need to isolate just the date portion of the string to pass to StringToDate()--the Left() function would work well here, or a substring.

Regards,

[mac4rfree85]

@jwiles, i am trying to process it to a date.
I used the left function to cut out the date alone and then i passed it to the StringToDate also.. Even then, i am getting the same error...

Code: Select all

StringToDate(Left(Trim(Colname),8),"%yyyy-%mm-%dd")

Regarding the dashes/slashes, do we really need to have it??? Datastage will not handle it from its side?

[kbsuryadev]

Try this.

StringToDate(Col,"%yyyy%mm%dd").Remove (-) inside the function.

By the way what is your target. Database or File.?

[Shruthi]

If nothing of that is working, u can also do this

Code: Select all

StringToDate(ColName[1,4]:"-":ColName[5,2]:"-":ColName[7,2])

[chulett]

Regarding the dashes/slashes, do we really need to have it??? Datastage will not handle it from its side?

You need to remove them. The mask needs to match what your incoming source field looks like and yours does not have any such delimiter.

[chulett]

If nothing of that is working, u can also do this

Code: Select all

StringToDate(ColName[1,4]:"-":ColName[5,2]:"-":ColName[7,2])

Not sure why U (one of our posters) would do this and a couple of issue here as well. One is that you are missing the format mask that the function needs. The other is that there's really no need to substring your source and introduce a delimiter when all you have to do is provide a proper mask that matches the incoming format.

[mac4rfree85]

Yes, %yyyy%mm%dd solved the issue... it did not occured to me that it is the input format and not the output format required as i thought...
Thanks for your help guys.. you ppl Rock!!!!!!!!!!!!

[chulett]

It's an all too common mistake. You need to realize there is no "output format" for a Date field... a date is a date is a date and it is stored in an internal/binary format. Only people readable external representations like in your string need a "format".