Date

ntr · Post by **ntr** » Tue Nov 22, 2011 5:03 pm

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,

karthikdsexchange · Post by **karthikdsexchange** » Tue Nov 22, 2011 5:28 pm

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.

kogads · Post by **kogads** » Tue Nov 22, 2011 5:33 pm

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

ntr · Post by **ntr** » Tue Nov 22, 2011 5:38 pm

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,

karthikdsexchange · Post by **karthikdsexchange** » Tue Nov 22, 2011 5:52 pm

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.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 22, 2011 6:23 pm

Use %m and %d designators for flexible format.

jwiles · Post by **jwiles** » Wed Nov 23, 2011 12:35 am

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,

chulett · Post by **chulett** » Wed Nov 23, 2011 8:21 am

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?

jwiles · Post by **jwiles** » Wed Nov 23, 2011 10:23 am

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,

ray.wurlod · Post by **ray.wurlod** » Wed Nov 23, 2011 1:13 pm

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

chulett · Post by **chulett** » Wed Nov 23, 2011 3:22 pm

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

jwiles · Post by **jwiles** » Wed Nov 23, 2011 4:30 pm

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

DSXchange

Date

Date

Re: Date