7 Replies Latest reply on Jun 28, 2011 9:24 AM by jc_sc

    Converting text date "yyyy/mm/dd" to filemaker date

    bagheraa

      Title

      Converting text date "yyyy/mm/dd" to filemaker date

      Post

      Hello.

       

      I'm a new FileMaker Pro 11 user.

       

      I have imported a database to filemaker and all worked fine, but I have a problem with the dates.

      In the original db, dates where stored as text fields with the format "yyyy/mm/dd".

      The number of records of the table is about 4500.

      The question is: How can I convert this fields to FileMaker date fields?

      Is there an automatic way to do it?

       

      Thanks.

      Enrique.

        • 1. Re: Converting text date "yyyy/mm/dd" to filemaker date
          aammondd

          You  can set up a calculation field 

          If there are no leading zeros on the months or days then you may have to do some Case testing before manipulating the string

           

          I think the calc looks something like this if your FM date format is mm/dd/yyyy

          You just need to arrange it as your FM date format within the text string portion of the GetAsDate Function

           

           

          GetAsDate ( Middle(datefield6,3)&Middle(datefield,9,2)&"/"&Middle(datefield,1,4))

           

          • 2. Re: Converting text date "yyyy/mm/dd" to filemaker date
            raybaudi

            This calculation takes in account even dates like this:

            2010/5/1

             

            Let([
            adj =Substitute ( YourImportedTextField ; "/" ; ¶ ) ;
            month = GetValue ( adj ; 2 ) ;
            day = GetValue ( adj ; 3 ) ;
            year = GetValue ( adj ; 1 )
            ];
            Date ( month ; day ; year )
            )



            • 3. Re: Converting text date "yyyy/mm/dd" to filemaker date
              hiatts

              Set this up as a custom function. 

               

              You hand it the date value in text, and then specify the date format ie "DD.MM.YYYY"

               

              It caters for several date formats, beauty is you can then use Replace Field Contents across the recordset, and have all the dates converted to a FMP Date format, and inserted into a date field

               

               

              Function Name:

              SAP Date String to FMP Date

               

              Function Parameters:

              DateString

              DateStringFormat

               

               

              Function:

               

               

              // Author: me

              // This function takes in a date string format, ie DD.MM.YYYY and a date value and converts it from a string into a date item that can be used in a date field and in date calculations.

              // Can be used in ReplaceFieldContents functions to update a found set of records without the need to loop, can also be used in field level calculations. Input format must match the users default date setting as displayed in transaction SU3

               

              Let (

               

              [ Dte = Trim ( DateString );

              DteFmt = Trim ( DateStringFormat ) ;

              Delim = Left ( Filter ( Dte ; "./-") ; 1 ) ;

              P1 = Position ( dte ; Delim ; 1 ; 1 ) ;

              P2 = Position ( dte ; Delim ; 1 ; 2 ) ;

              Len = Length (dte)

              ] ;

               

              Case (

               

              "DD.MM.YYYY" = DteFmt ;

              Date ( Middle ( dte ; P1 + 1 ; P2 - P1 ) ;

              Left ( dte ; P1 - 1 ) ;

              Right ( dte ; Len - P2 )

              ) ;

               

              "MM.DD.YYYY" = DteFmt or "MM/DD/YYYY" = DteFmt or "MM-DD-YYYY" = DteFmt ;

              Date ( Left ( dte ; P1 - 1 ) ;

              Middle ( dte ; P1 + 1 ; P2 - P1 ) ;

              Right ( dte ; Len- P2 )

              ) ;

               

              "YYYY.MM.DD" = DteFmt or "YYYY/MM/DD" = DteFmt or "YYYY-MM-DD" = DteFmt ;

              Date ( Middle ( dte ; P1 + 1 ; P2 - P1 ) ;

              Right ( dte ; Len- P2 ) ;

              Left ( dte ; P1 - 1 )

              ) ;

               

              Date ( 0 ; 0 ; 0 ) )

               

              )

              • 4. Re: Converting text date "yyyy/mm/dd" to filemaker date
                RickWhitelaw

                "I refuse to have a battle of wits with an unarmed person."

                 

                I don't wish to start a flame war, but I'm sure I'm not the only person who finds having to read (or even notice) this signature, a tiresome thing. I would want to make sure I was "heavily armed" before committing to a signature like this. Besides, it's a tired old joke . . . sorry.

                 

                RW

                • 5. Re: Converting text date "yyyy/mm/dd" to filemaker date
                  comment_1

                  Since you brought it up, I'll add my two cents: I don't mind the content of the signature, but the lack of any separation between it and the body of the message can be irritating. A couple of times I even had to read it again in order to realize it wasn't meant to insult the other party.

                   

                  On a more general note, I find the whole business of signatures rather childish.

                  • 7. Re: Converting text date "yyyy/mm/dd" to filemaker date
                    jc_sc

                    Thanks Raybaudi, your answer worked perfectly.