4 Replies Latest reply: May 15, 2014 10:03 AM by TamTJB _ RSS

    Date conversion creating false record

    TamTJB _

      Hello,

      I have a model which contains a series of Calc Fields (created by someone else) that convert a funky YYMMDD date field from our database.  For item records for which there is no[/B] information in this field, the Calc Fields are creating a false record.  Examples:

       

      Orig. date field from database YYMMDD[/B]         

      80917                                             

      (blank)        

       

      Converted date field MM/DD/YYYY[/B]    

      9/17/2008

      12/30/1899

       

      What I've tried to do is create another Calc Field with an 'if' statement to leave the date field blank[/I] if it equals 12/30/1899, but return the converted date if it does not.  I've tried a few variations, but there's something I'm obviously missing because I keep getting an error message about the operand types.  Does anyone have any suggestions?  (If it would be helpful to see the series of Calc Fields, let me know)

       

      Thank you!

        • Date conversion creating false record
          Data Kruncher

          Hi Tam,

           

          It sounds like your needs are similar in nature to another member's challenge [URL="http://www.monarchforums.com/showthread.php?t=2703"]posted recently[/URL].

           

          What you're after, I believe, is:

          If(ConvDate={1899-12-30},Date(CtoD("")),ConvDate)[/CODE]

           

          where Date(CtoD("")) will generate a null value allowed by your Date type calculated field.

           

          Then you can use the Options View menu to determine what Monarch displays for null values (display null values as).

           

          HTH,

          Kruncher

            • Date conversion creating false record
              TamTJB _

              Hi Tam,

               

              It sounds like your needs are similar in nature to another member's challenge [URL="http://www.monarchforums.com/showthread.php?t=2703"]posted recently[/URL].

               

              What you're after, I believe, is:

              If(ConvDate={1899-12-30},Date(CtoD("")),ConvDate)[/CODE]

               

              where Date(CtoD("")) will generate a null value allowed by your Date type calculated field.

               

              Then you can use the Options View menu to determine what Monarch displays for null values (display null values as).

               

              HTH,

              Kruncher[/QUOTE]

               

              Actually, that other post is exactly what spurred me to ask my question.  And your solution seems to have worked perfectly, so I appreciate your help.  The CtoD is exactly what I was missing. 

               

              Thank you, Grant, as well!

            • Date conversion creating false record
              Grant Perkins

              Bear in mind that all date fields are really interpreted from the underlying data and the interpretation of a blank field, depending on the start year set for the reference calender, will return 12/30/1899 in most cases.

               

              If you really want a blank you may need to convert the field content to Character using a calculated field and the DtoC() function and then some conditional processing. You may also need to include this in some form of IF statement.

               

              That would be one way to deal with it, there will no doubt be many others.

               

               

              HTH.

               

              ETA: Darn, should have waited a few more minutes until Kruncher posted and so would have saved some time ...

               

              Grant