Date conv works in Debug w/ limited load but not in full Reload???
I am loading 3 new datetime fields into a QV temp table. Then, I am converting them from the date that QV translates it to. This is working, no problem…. WHEN I DO A LIMITED LOAD IN DEBUG!!! When I do a full RELOAD, it blanks out the new fields. I have even tried doing a 500,000 line load (which is more than the RELOAD amount would be) and it still works. Have you seen anything like this? We tried on 2 different machines thinking that it possibly was a memory issue, but we have ruled that out. I have pasted the code and my results below in case someone can see something glaringly wrong. Anyone else had this issue? Ideas to try? I've looked at the Windows Event Viewer, checked the log file, looked at my data to make sure there are no NULL values coming in, etc. I am at loss.
raw_LNL_Agent_ContractCode_History: Select WEEK as 'lach.EffectiveWeek1', //This Field AGT as 'lach.Agent_No1', UM as 'lach.UMgr1', BM as 'lach.BMgr1', SA as 'lach.SA1', RAD as 'lach.RAD1', Partnership as 'lach.Partnership1', DISTRCT as 'lach.District1', AGY as 'lach.Agency1', GRP as 'lach.Group1', HIRE as 'lach.Hire_Date1', //This Field ReportWeek as 'lach.ReportWeek1', // This Field BranchOffice as 'lach.BranchOffice1' from LNAGUMBM ;
LNL_Agent_ContractCode_History: LoadDate(floor(timestamp#(lach.EffectiveWeek1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD') aslach.EffectiveWeek, //This Field lach.Agent_No1aslach.Agent_No,
Re: Date conv works in Debug w/ limited load but not in full Reload???
Maybe when you do a full reload there are data that don't have a valid date format. I think Alt() function could help you, you can use this function to check if the fild has the format or formats you need, something like this:
Alt(Date(floor(timestamp#(lach.ReportWeek1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD'), 'Invalid Date Format')