Duplicate records being created when Month, Year etc are applied to a bad date

[#1]

Hi,

I have a bad date format and am uisng the following code to extract the Date, Year, and Month.

Thanks to those who helped with thi sbefore.

However multiple records are being created when I try to extract the Date, Year and Month.

Can anyone suggest why this is occuring. I've attached a simple spreadsheet and QV app. to replicate the problem.

All help appreciated.

Tony

[Jason_Michaelides]

I can't open the qvw right now so can't see what you're using to extract the date.  Looking at the spreadsheet example I'd try:

Date(Date#(TRIM(TextBetween(baddate,' ','/')),'MMMM YYYY'))     AS     GoodDate

Hope this helps,

Jason

[swuehl · Accepted solution]

You are getting multiple records, because you are using subfield() function without stating the sub field to take, this will create a record per subfield found. You use subfield twice in your load, thus you get 2x2 records created per input record (having 2 sub fields in your input text).

If you want stick close to your original idea, just state the sub field to use with the third paramater to subfield():

date(date#(Trim(Subfield(mid( baddate,7),'/',1)), 'MMM YYYY') ,'DD/MMM/YYYY') as gooddate,

[#4]

Thanks swuehl ,

That's great,

I really appreciate your help.

I'd pretty much given up on getting a solution.

I've posted the solution in case any else has the same problem.

Tony