DSXchange

Hi,
Iam facing issue from 2 days can any one help on this
My input is
3/21/2001 i want to convert into date fomat
i am using the below function

(StringToDate(Right(Str('0',1):Field(lnk_to_trsf_validation.D_TRADE_DATE,"/",1,1),2):'/':Field(lnk_to_trsf_validation.D_TRADE_DATE,"/",2,2),"%mm-%dd-%yyyy"))
when i am running this i am not able to load into the target.
with warnings and error

Warnings: Data string '0/' does not match format '%mm-%dd-%yyyy': the value for tag %mm has fewer characters than expected.
Conversion error calling conversion routine date_from_ustring data may have been lost.


Please help me on this.

Thanks & regards,

StringToDate('03/21/2011',"%mm/%dd/%yyyy") . This will do.
Initially please know the format of your D_TRADE_DATE.
Then it will be easy for conversion.
For that use peek to debug the date format.

StringtoDate function expects the second argument as how the input date format is arriving and not how you want the output to look like.

Hi,

I am getting the format as 3/21/2011.
Today it will be like this for tomarrow it will be 11/21/2011.
So i use the aove function.

The function that is used was right?If not correct me if any wrong.

Thanks & regards,

Use stage variable which will do the check on the length of the string.
If length is 10 use same date else 0:date
In the ETL translation, use the function StringToDate.
I recommend this way to make debugging easy for you. Use this as tip.

Use %m and %d designators for flexible format.

ntr,

You posted this same question a few days ago and were answered with what should have been the correct solution (which uses what Ray suggests in his reply). Did you try the suggested solution? Did it work?

Your earlier thread: http://dsxchange.com/viewtopic.php?t=143247

Regards,

James - you don't need any special tags or that 'sid' portion, just the simple URL pasted in works all by itself. FYI.

Ray - sorry for the (slight) derail but when did the single character date masks become 'flexible format' masks? I thought (back in the day) that %m matches one-digit months and %mm matches two-digit months and never the twain shall meet. Older posts have shenanigans to check lengths and stick a zero on the front of the 'short' months/days. Now they will happily match one or two digits?

Thanks for the tip, Craig!

I think the variable-width format option came in with v8.0. I don't see it in the docs prior to that (at least not in 7.5.1a).

Regards,

If you want a more self-documenting variant, you can use %(m,v)

Thought it was 's' rather than 'v'... or is that yet another variant?

V for Vendetta....wait, wrong reference

'v' with %ddd (only) to indicate variable-width day of year. "(%ddd,v)"

's' with several of the formats to allow leading spaces. day, month, 2-digit year, and week of year

plus a few scattered ones for formatting day and month strings