2 Replies Latest reply on Feb 28, 2013 8:27 AM by KrisztianG,

    Converting text date



      Converting text date



           I have ~ 9000 records imported as text in the wrong format.

           Each date looks like: 2012.01.23. with a dot at the end. How can I convert these text fields to dates in the format 2012.01.23 without the dot at the and to make eg. sorting possible?

           Many thanks!

        • 1. Re: Converting text date

               Add a second field to your table of type date. You'll want it to be of type date so that the dates sort correctly.

               Do a show all records, put the cursor in the new date field and use Replace field contents to convert the text to an actual date in your table:

               Replace Field Contents [YourDateField ; Date ( Middle ( YourTextField ; 6 ;2 ) ; Middle ( YourTextField ; 9 ;2 ) ; Left ( YourTextField ; 4 ) ) ]

               "YourTextFIeld" is what I am calling your current field with the imported date.

               Note that dates are stored internally as a number when entered into a field of type date. You can use data formatting in the Inspector to specify any date format that you need for display on your layouts.

          • 2. Re: Converting text date

                 That helped me a lot!

                 Many thanks and sorry for my late answer!