7 Replies Latest reply on Mar 6, 2013 5:01 PM by keywords

    How to determine the internal date format of a file

    BowdenData

      I am looking for a way to determine the internal, fixed date formatting of a FMP file, i.e., mm/dd/yyyy or dd/mm/yyyy, and so forth. I am aware of how the format gets set for a new/cloned/recovered file.

       

      I could have sworn that the DDR XML data contained this information, but I am not seeing it now. This is using FMP11. I found one article at

       

      http://blog.myfmbutler.com/?p=495

       

      but their example of using in a case statement has me baffled. I loaded up the doSQL plugin and copied the exact formula they suggest, but it gives me an error from the plugin.

       

      Doug

        • 1. Re: How to determine the internal date format of a file
          ch0c0halic

          Why do you need to know its internal storage? As long as you use the Date functions you will always get the value you want. FMP knows what the date format at time of entry was for the date value. So regardless of how it was entered FMP can always extract and convert values correctly using the Date associated calculation functions.

           

          For example to reformat any date value for the current OS settings you can do this:

          Functions:               Date ( month ; day ; year ), Day(), Month(), Year(), Get(CurrentDate)

           

          Calculation-Date results:     Date ( month ( Get ( CurrentDate ) ) ; day ( Get ( CurrentDate ) ) ; year ( Get ( CurrentDate ) ) )

          Will result in todays date displayed in whatever date format is currently used on the computer. Replace Get(CurrentDate) with your date field and you can reformat the value (as a date) to the current OS date display preference.

           

           

          No matter what the stored value format is these functions will automatically extract the desired value. Its up to you to use them appropriately.

           

           

          The opinions expressed in this email are my own and do not reflect those of my employer or anyone else.

          Regards,

          Ch0c0halic, FileMaker 12 Certified Developer

          • 2. Re: How to determine the internal date format of a file
            keywords

            On a Mac I believe the date format is set in the System Preferences >> Language & Text. This will lead to switching between dd/mm/yyyy or mm/dd/yyyy etc depending on the standard selected. Regardless, internally FM stores each date as a number (7 Feb 2013, however it is rendered, is 734906).

            • 3. Re: How to determine the internal date format of a file
              AlanStirling

              Hi Keywords

               

              Please forgive me for correcting two statements that you have made in your recent post.

               

              1). There is only one way to change the date format of an existing FileMaker database. To do so, you start by making a clone of the file (using File menu>Save a copy - Select Clone) on a machine with the OS set to the date format that you want to use and then import all the data from the original file back into the clone.

               

              2). I have inspected the internal date format used by FileMaker Pro, as stored within a FileMaker Database on disk, and I was surprised to find that dates are stored in exactly the style they had been entered - for example 'dd/mm/yyyy' here in the UK and mm/dd/yyyy in a US file. Although it would seem logical to have stored the number of days since 1/1/0001, this is not the case.

               

              Best wishes - Alan Stirling, London UK.

               

              Alan Stirling Technology Ltd, 135 Lisson Grove, London NW1 6UP

              +44 (0) 20 7724 2456 - alan@ast.fm - www.ast.fm.

              FileMaker Certified Developer for versions 7, 8, 9, 10, 11 and 12.

              • 4. Re: How to determine the internal date format of a file
                BowdenData

                Alan,

                 

                How did you inspect the internal format?

                 

                Doug

                • 5. Re: How to determine the internal date format of a file
                  BowdenData

                  Ch0c0halic,

                   

                  As usual, there is more to the story, but I wanted to be brief in my request. Thanks for summarizing the use of the Date function, etc. I am familar with this, so that is not the issue.

                   

                  We have several files in a solution that is quite large/complex overall. We know that most or maybe all of the files were originally created with UK dd/mm/yyyy formatting. A lot of new functionality was added on a DEV copy of the system. When the system was upgraded, the dev files were cloned and production data was imported into the clones. The clones were made/opened on a PC with US mm/dd/yyyy date formatting. This was an oversight.

                   

                  Now, we have dates that are not printing out correctly, scripted date searches are not working in some cases, field labels are wrong, and so forth.

                   

                  In correcting this, we are going to take the current production files offline, clone on a PC set to UK formats and re-import the data. We want to make absolutely sure which original files have the UK internal date format vs. any that have US internal format.

                   

                  I believe that I can do this via doing sample searches, but wanted to see if there is anything else out there. As mentioned, the DDR XML used to show this clear as day. Don't know when it changed - might have been a long time ago now.

                   

                  Doug

                  • 6. Re: How to determine the internal date format of a file
                    ch0c0halic

                    My question is how did you get the data out of the production system? If you exported the data to a text file type, like comma-separated-value (.csv), then by definition those are text values, not dates. This would explain why the date formatting isn't being maintained.

                     

                    If you transfer the data from one FMP file to another, with date fields on both source and destination, then FMP will import the dates with the original input formatting so it can format the date correctly when displayed using a different OS format.

                     

                    I recommend either importing directly from the original databases into the clones or exporting the data from production to individual FMP files and then importing them into the clones.

                     

                     

                    The opinions expressed in this email are my own and do not reflect those of my employer or anyone else.

                    Regards,

                    Ch0c0halic, FileMaker 12 Certified Developer

                    • 7. Re: How to determine the internal date format of a file
                      keywords

                      Corrections noted, thanks, but with a couple of provisos:

                       

                      1.     When I change system preferences, dates in an existing FM database WILL change provided you have selected File Options >> Text >> Data Entry >> Always use current system settings (see screenshot).

                      2.     In my own defence I quote from Ray Cologon's Filemaker Pro 10 Bible: "FileMaker stores all dates internally as a numeric value representing the days since 1/1/0001 inclusive." (p470). The calc GetAsNumber ( date ) will give you a number, which is days since 1/1/0001, as per my initial example. Ray goes on to say: "FileMaker RECEIVES and DISPLAYS [my emphasis] accepted date formats (according to the current file or system regional settings)"

                       

                      Screen Shot 2013-03-07 at 11.46.17 AM.png