12 Replies Latest reply: May 15, 2014 9:53 AM by Shep _ RSS

    Date as CYYMMDD

    Shep _

      I've read some of the posts about formatting dates and will be trying to apply the tips but didn't see this particular layout.

       

      I have a column of dates within my table.

      For export these dates are expected to be reformatted based on the following:

       

      Use a 'modified date' being seven positions (CYYMMDD).  The century indicator (C) for modified dates is "0" for dates prior to 2000 and "1" for all others.

       

      3/14/2007 needs to be 1070314

      5/15/1996 needs to be 0960515

       

      The resulting export of this field (amongst the other data) can be textcharcter. It is headed to Excel for reviews and finally to '.csv' for an upload.

       

      Any suggestions or words of caution would be appreciated.

        • Date as CYYMMDD
          Nick Osdale-Popa

          Here's the formula I came up with:

          [font="courier"]If(Year(MyDate)<2000,"0","1") + Right(Trim(Str(Year(Mydate))),2)Right("0"Trim(Str(Month(MyDate))),2) + Right("0"+Trim(Str(Day(MyDate))),2)[/font][/quote]Be sure that the Calculated Field is set to character.

          • Date as CYYMMDD
            Shep _

            Thanks for the example.

            I began to work on my model(calc field) and realized the dates were provided as 03/15/07 or 05/15/96. (versus 3/15/2007)

             

            I've used LSplit(s), calc, concatenate and did resolve to expected value.  But, I feel like I've got to many stepsfields (even though I can 'hide' them) wherease it seems it could be all one field(formula)

             

            It may be sloppy   :rolleyes:  as my first effort but I'm glad to have MONARCH and such options.

            • Date as CYYMMDD
              Nick Osdale-Popa

              As long as your date is a true date and not a character field, then my formula still work as intended. Monarch will parse the year for 03/15/07 to 2007.

               

              Hope that helps!

              • Date as CYYMMDD
                Shep _

                Well. . .it looks like it was originally interpreted as character.  If I change properties to 'date' - I get a bunch of 'NULL'

                Do I have to back up and try to bring the report in a different manner?

                • Date as CYYMMDD
                  Grant Perkins

                  Originally posted by Shep:

                     But, I feel like I've got to many stepsfields (even though I can 'hide' them) wherease it seems it could be all one field(formula)

                   

                  /b[/quote]Shep,

                   

                  You could think of this (and any future complex calculated fields) as being built from building blocks. If you develop a field to get each smaller part of the calcualtion to work correctly it is easier than trying to make the whole thing in one attempt.

                   

                  Once the parts are working correctly you can just combine them all into one field by concatenation (for example) as Nick has done (with the + sign).

                   

                  If you had developed the parts separately the combined field would be

                   

                  +

                   

                  That would give your answer.

                   

                  Once happy that everything works just replace the name of the field with the formula that created it in the "concatenation" field. Then you can delete the 'development work' field as it would no longer be needed.

                   

                  Does that make sense as a way to develop a complex formula but end up with a neat, single field in the table?

                   

                   

                  HTH.

                   

                   

                  Grant

                   

                  PS.

                   

                  As you have V9 you could also consider making your resulting formula (and even the intermediate step formulae) into a User Defined Function so that it will be easily available for future re-use.

                   

                  Or maybe as a Linked Object field from a "special object" model.

                  • Date as CYYMMDD
                    Grant Perkins

                    Originally posted by Shep:

                    Well. . .it looks like it was originally interpreted as character.  If I change properties to 'date' - I get a bunch of 'NULL'

                    Do I have to back up and try to bring the report in a different manner? /b[/quote]Shep,

                     

                    Whereabouts are we in the process?

                     

                    Importing to your final target system AFTER the Monarch export or are you still in Monarch here?

                     

                     

                    CYYMMDD is not, so far as I am aware, a readily recognised date format - hence the nulls.

                     

                    Grant

                    • Date as CYYMMDD
                      Shep _

                      Where I'm at = Still in the Monarch Table

                      1.) I have a text file representing dates as

                      06/30/05 or 12/31/94

                       

                      2.) I created a template to pull out the various desired data.  Monarch table displaying everthing I want. 

                       

                      *Then I started trying to do the CYYMMDD stuff.

                       

                      Based on Nick questioning Char vs Date of the original date field, I reopend the report     emplatemodel and found the table column that has the original dates are set to CHAR.  Not sure if I did that or Monarch.

                      While in that table and I change properties of that column from CHAR to DATE - display result is mostly NULL with a few obviously wrong dates displayed

                      • Date as CYYMMDD
                        Data Kruncher

                        Shep,

                         

                        Go to your Options menu, then Input.

                         

                        The first option, Date Format, is probably set to D/M/Y. Change it to M/D/Y and you should be OK, all the nulls will show as regular dates.

                         

                        Kruncher

                        • Date as CYYMMDD
                          Grant Perkins

                          Originally posted by Shep:

                          Where I'm at = Still in the Monarch Table

                          1.) I have a text file representing dates as

                          06/30/05 or 12/31/94

                           

                          2.) I created a template to pull out the various desired data.  Monarch table displaying everthing I want. 

                           

                          *Then I started trying to do the CYYMMDD stuff.

                           

                          Based on Nick questioning Char vs Date of the original date field, I reopend the report     emplatemodel and found the table column that has the original dates are set to CHAR.  Not sure if I did that or Monarch.

                          While in that table and I change properties of that column from CHAR to DATE - display result is mostly NULL with a few obviously wrong dates displayed /b[/quote]Shep,

                           

                          It may depend on how your Input Options are set as far as date formats are concerned. Hence the CHAR definition for the field.

                           

                          From what you originally said you simply need to output CYYMMDD. With C being an interpreted character based on the century being 19 or 20.

                           

                          In that case it really does not matter to you whether the date field interpets as a date or not. You can simply treat is as a character field (to keep things simple) and format it as per Nick's formula for the export.

                           

                          I assume that the target application's import process will be able to deal with the format you have been asked to provide in the Monarch output. In which case it really does not matter what Monarch interprets the fields to be. Character format is much easier to work with here than date format would be and seems to be all that you need.

                           

                          Or have we missed something important along the way?

                           

                          Grant

                           

                          [size="1"][ June 28, 2007, 05:21 PM: Message edited by: Grant Perkins ][/size]

                          • Date as CYYMMDD
                            Shep _

                            But Nick said "As long as your date is a true date and not a character field".

                            I assume he is talking about the date field originally read in versus the calc-field I'm creating.

                             

                            FYI. . .CYYMMDD is a "modified" date format expected by a mainframe file.  Once I've exported from Monarch to Excel and the workbook content is approved the worksheet will be saved as comma delimited text for upload to mainframe.

                            • Date as CYYMMDD
                              Grant Perkins

                              Originally posted by Shep:

                              But Nick said "As long as your date is a true date and not a character field".

                              I assume he is talking about the date field originally read in versus the calc-field I'm creating.[/b][/quote]Yes. The formula will work for a date field. If the field is a char field the formula can be written a different way of converted to a date field as part  of teh formula calculation.

                               

                              If Monarch does not recognise the MM/DD/YY format it is likely just the Options> Input > Date/Time and number translation settings that are problematic. Check the settings are set to recognise M/D/Y date format and that the Extraction Patterns you expect to find are also correctly set (At least the Date box should be ticked.)

                               

                              The imput settings are defaulted but any variation on the default (whatever you have set) will be saved with the model for future use. If you want to change the default for future use you shoudl tick the 'Make these the default settings' box.

                               

                               

                              [b]

                              FYI. . .CYYMMDD is a "modified" date format expected by a mainframe file.  Once I've exported from Monarch to Excel and the workbook content is approved the worksheet will be saved as comma delimited text for upload to mainframe. /b[/quote]If the output into Excel is 'character' it should not matter to you. If the mainframe demands a char or number format which is not a date that is not  your problem as far as Monarch goes! Presumably the load of the output to the mainframe can correctly interpret the csv file. I think we agree on that requirement from what you have said.

                               

                              Does this help?

                               

                              If not I am puzzled.

                               

                               

                              Grant

                              • Date as CYYMMDD
                                Shep _

                                OK: I've backed up, dbl-chk Monarch settings, and have seen Nick's code work as provided.

                                 

                                Flowed from Monarch Table to Excel to '.csv' file. ~~Thank you to all contributors.

                                 

                                Grant: I appreciate the 'building block' analogy. 

                                I'll keep that approach in mind and attempt to apply it in another similar instance on my "to do" list.

                                 

                                &- The mainframe upload should be happy as I'm simply feeding a text file

                                . . .that now, after all this, has correct looking values.

                                 

                                *Good night   :cool: