11 Replies Latest reply on May 20, 2011 11:43 PM by MadMacs

    Date Format Problem?



      Date Format Problem?


      Hi, hope someone can help. I imported a database from Bento which was fine, this included a date field in short form ie month, day and year but without time. Firstly when I try to do a search on the imported data it shows a nil return even though there are dates within the range searched. Secondly, if I perform a search on data added since the import it works fine, this suggests there is a formatting difference between the imported and newly entered data. The FMP database is set-up as a Date field, validated during data entry with user overide and 4 digit year. I have tried both indexed and non indexed. I am not interested in the time from a display point of view but appreciate it may be important because of the way dates are calculated 1-4000. I have tried exporting the field to numbers, cleaning it up and then reimporting but this did not work. I have tried recovering the database in case there was a problem but no errors were thrown up and the recovered database behaves no differently. There's probably something very obvious that I'm missing...any suggestions?  Many thanks.

        • 1. Re: Date Format Problem?
          "...this included a date field in short form ie month, day and year but without time." A FileMaker date field doesn't contain any time, a timestamp field does. Try to import the Bento's date in a simple text field and tell us what you get.
          • 2. Re: Date Format Problem?

            Thanks Raybaudi. The timestamp explanation makes sense! I tried a text export from Bento in both CSV and Tab file, created a new text field with the same name to accept the dates but FMP won't allow me to import. I tried changing the field type from text to date in FPH but it still will not accept the import. The text export from Bento is in the form 9 Sep 2009 whereas the dates in FMP are 09/09/2009, I'm not sure why but would this make a difference when importing as text?

            • 3. Re: Date Format Problem?

              FileMaker won't recognize 9 SEp 2009 as a date.

              Why not import directly from Bento?

              • 4. Re: Date Format Problem?

                I tried to import directly from Bento but get the error message that I need to be at v 2 or above, even though I'm running v4! I have now managed to import the data as text and its showing the US date format mm/dd/yyyy. I can manually update the 20 or so records but I would ideally like it to be in European format dd/mm/yyyy. Can/should I convert field to date or will I end up with the same problem of mixed formats where I can't search on date ranges? Many thanks.

                • 5. Re: Date Format Problem?

                  Correction, US date format is in fact yyyy/dd/mm and not mm/dd/yyyy. 

                  • 6. Re: Date Format Problem?

                    Once the data is input as a date into a date field without error, you can use any date format you want to display the date.

                    There have been issues with importing from Bento into FileMaker and I've logged them in the Known Bug List as they were reported and then confirmed by FileMaker Inc, but all but one appear to have been fixed by updates to Bento. The remaining issue doesn't match what you describe.

                    Make sure that Bento is closed when you select the file for import.

                    Make sure that you have the most recent updates of Bento.

                    • 7. Re: Date Format Problem?

                      Tried changing field type from Text to Date but this now shows "?" in the date field for each record entry. Click on the field and it reverts to yyyy/mm/dd. I'd rather not have to manually recreate over 4,000 dates!

                      • 8. Re: Date Format Problem?

                        You shouldn't need to. If all else fails, you can inport the dates as text and then use Replace Field Contents with a calculation that computes the date from the imported text and copies it into a date field.

                        Say you add a field to your table of type date, keeping your current dates in text fields, named DateText...

                        Let ( [ D = Leftwords ( DateText ; 1 ) ;
                                  Y = RightWords ( DateText ; 1 ) ;
                                 M = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Middlewords ( dateText ; 1; 1 ) ; 1 ; 1 ) / 3 ) ] ;
                                 Date ( M ; D ; Y )

                        To use this, do a show all records, click into the new date field, select Replace Field Contents from the Records menu and use the above expression with the calculation option. (Make sure to use your field name for the original date field in place of "DateText".)

                        • 9. Re: Date Format Problem?

                          Phil -- Thanks for a FABULOUS FORMULA that could be adapted in numerous scenarios

                          • 10. Re: Date Format Problem?

                            Oops, and that should be Middlewords ( dateText ; 2; 1 ) Embarassed

                            • 11. Re: Date Format Problem?

                              Thanks Phil for the elegant solution which I'm sure could also be adapted to suit other criteria. It now works fine and can be searched.