1 Reply Latest reply: Sep 20, 2013 2:15 PM by Jose Tos RSS

    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:
      Load Date(floor(timestamp#(lach.EffectiveWeek1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD') as lach.EffectiveWeek//This Field
      lach.Agent_No1 as lach.Agent_No,

           lach.UMgr1 as lach.UMgr,
      lach.BMgr1 as lach.BMgr,
      lach.SA1 as lach.SA,
      lach.RAD1 as lach.RAD,
      lach.Partnership1 as lach.Partnership,
      lach.District1 as lach.District,
      lach.Agency1 as lach.Agency,
      lach.Group1 as lach.Group,
      Date(floor(timestamp#(lach.Hire_Date1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD') as lach.HireDate, //This Field
      Date(floor(timestamp#(lach.ReportWeek1,'M/D/YYYY h:mm:ss[.fff] TT')),'YYYY-MM-DD') as lach.ReportWeek //This Field
      RESIDENT raw_LNL_Agent_ContractCode_History    

        

      DateResults.jpg

        • Re: Date conv works in Debug w/ limited load but not in full Reload???
          Jose Tos

          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')

           

          You should review the syntax in the help menu...

           

          Hope this helps

           

          Regards