Concatenate Date Fields

[gfisch13]

Hi Folks - I'm working with an excel file that has a date field format that I'm having trouble with  so I thought I'd break the date field into separate components and then rebuild the date.

This is what the portion of my script looks like:

[Issue date ],
// Date([Issue date ], 'MM/DD/YYYY') as IssDt,
Left([Issue date ], 4) as IssueYr,
Mid([Issue date ], 5,2) as IssueMonth,
Right([Issue date ], 2) as IssueDay,
Date([IssueMonth] & [IssueDay] & [IssueYr], 'MM/DD/YYYY') as IssueDt,

I keep getting an error that says Field not found error, 'IssueMonth' not found.   What am I doing wrong?

I've tried everything from changing the format in Excel to changing formats in QV.  This seemed like the easier path based on everything else I tried but I'd like to understand what I'm doing wrong in the script.

Thanks!!  George

[stevejoyce]

In your IssueDt field, you can't reference fields that haven't been created yet (i.e. IssueMonth & IssueDay).

You can do this with a preceeding load, or repeat the same logic, but when you are creating IssueDt, these fields don't exist yet:

preceding load example:

table:

load

*

,Date([IssueMonth] & [IssueDay] & [IssueYr], 'MM/DD/YYYY') as IssueDt

;

load

[Issue date ],
left([Issue date ], 4) as IssueYr,
Mid([Issue date ], 5,2) as IssueMonth,
Right([Issue date ], 2) as IssueDay
from <table>

[sunny_talwar · Accepted solution]

Try this

Date(MakeDate(Left([Issue date ], 4), Mid([Issue date ], 5,2), Right([Issue date ], 2)) as IssueDt

[gfisch13]

Thank you both for your replies.  I really wanted to stay away from the preceding load as I'm not too familiar with the procedure.  The Date function seemed like the right path i just didn't get the syntax correct.

[stevejoyce]

single load works if you only needed date.  if you wanted all the partial fields (year, month, day) AND the date field, you'd only have to define your partial fields 1x, and then use those definitions in your date field -> centralize logic. 

Or vice versa to what i originally posted, first create your date field, then your preceeding load can reference that date field and create partial fields in a single load pass.

Maybe not big here, but something to keep in mind in future.