8 Replies Latest reply on Oct 10, 2008 3:03 AM by AndreasT

    Passing dates as numbers



      Passing dates as numbers


      It has been suggested in the FileMaker Training Series materials that dates in FileMaker Pro are stored as numbers. However, international date issues suggest otherwise. In an effort to get consistent interpretation of dates between US formatted and International formatted systems, we have proposed passing dates (in parameters) as numbers.


      So you can convert a date to a number using GetAsNumber ( datefield ).

      If the date field contained 10/10/2008, this would return 733325. 

      This number is recognised by FileMaker Pro as  733325 days since Jan 1, 0001.


      So far so good.


      When the number is received, it needs to be converted back to a date.


      The online help says that the GetAsDate function requires text - any text expression or text field containing text in the same format as the date on the system where the file was created. 


      When you use this - GetAsDate ( "733325" ) it returns a ? which would be expected.


      However, both GetAsDate ( 733325 ) and GetAsDate ( GetAsNumber ( 733325 ) ) return a date.


      So the question is - is the documentation wrong or incomplete? Is this behaviour supported? Can you really pass a number to the GetAsDate function and have it return the correct answer?


      I suspect the same questions should be posed for the GetAsNumber function - it uses a text parameter which is "any text expression or text field containing numbers". So is it supported behaviour that you can pass a date to the function and have it return the number?

        • 1. Re: Passing dates as numbers



          Thank you for your post.


          Your observations are correct.  The documentation is incomplete.  That is, a date field is indeed stored as a number, and you can use a Number variable/field in the GetAsDate() function.  The same applies to the GetAsNumber() function with date variables/fields.


          I know this sounds defensive, but I don't consider the documentation as "wrong".  From my experience answering customer questions about dates and numbers, there have been very few instances where users want a number value from a date and/or return a date from a number.  A great majority just want to obtain a date from a text value, or return a number from a text value.  Please don't get me wrong...  I'm not saying your point isn't valid.  My main concern is if this information is to be added to the manual, will it confuse users?  That's a rhetorical question that can be discussed.  I'd like to know your thoughts.


          Thanks for pointing this out.



          FileMaker, Inc.

          • 2. Re: Passing dates as numbers

            Hi TSGal

            Thank you for your confirmation. Can I ask if that was checked with a FileMaker engineer? 


            As far as the documentation goes, as a developer I would prefer to have documentation that fully explains the behaviour of a function. I don't see any point in 'dumbing down' the technical details because it might confuse people.


            The issue here is a question of what is supported behaviour? In the past, developers have found workarounds for their issues that have then been broken with a future release because they were using unsupported behaviour that was 'fixed'. 


            In this case, if the GetAsDate function will accept a number as a parameter and convert that to a date, then the documentation should state that fact. Obviously, there would also be a link to the page detailing how FileMaker Pro stores and handles dates. 


            Knowing that this behaviour is supported would solve a current issue with the passing of dates as parameters in solutions created and used in different system locales.

            • 3. Re: Passing dates as numbers

              Hi David.


              Please excuse me if I misunderstand your request, but it seems to me that what you are looking for is a way to ensure that dates are formatted correctly according to local rules in various countries.


              Filemaker does have a native functionality to ensure that this always happens automatically.


              This setting is found under File Settings:Text and can be set to always use local settings or let the user be asked on startup.


              This requires that the date fields are stored as dates.


              I hope this helped you.

              • 4. Re: Passing dates as numbers

                Hi Andreas

                Thank you for your comments. However, it is a little more complicated than that. If you have a database created on a US formatted system which is opened on an Australian formatted system, there can be misinterpretation of dates when you store them as text and then set date fields with that text. FileMaker Pro does not handle this well. Yes, dates should be stored as dates but when you get into script parameters, a lot of things become basic text.


                As an example, I change my International region setting to United States and create a new database with one table. That table has a text field and a date field. I create a new record and enter today's date - it appears as 10/9/2008. this is correct as the current date is October 9, 2008.


                Now I quit out of FileMaker Pro and change the International setting back to Australia. On opening the database, the date is displayed (correctly for Australia) as 9/10/2008. The File Option for data entry is to 'Always use current system settings'. So far so good.


                If I script a process to set the date field to "30/9/2008" it will fail - the system considers it to be an invalid date. But the same script setting the field to "9/30/2008" will correctly set the field to 30/9/2008. When doing some text to date coercion, the file is using the original system region, not the current system region even though the File option is set.


                So why would you want to do this? It came up when a developer was using the "Property Lists" custom functions written by Shawn Flisakowski (http://www.spf-15.com/fmExamples/).  These functions create text type variables, and therefore do an implicit date to text conversion when adding a date to a property. 


                Anyhow, it breaks.

                • 5. Re: Passing dates as numbers



                  Thanks for your comments.  I have forwarded this entire thread to our Development and Software Quality Assurance (Testing) teams so they can consider it for a future release.


                  I apologize.  My point wasn't for "dumbing down".  My point was that the overwhelming majority of users would not use this.  I should have made it clearer that this could also be written up as a Knowledge Base article available on our web site.  In any case, the appropriate people have the thread and your comments.





                  FileMaker, Inc. 

                  • 6. Re: Passing dates as numbers

                    David, thanks for clarifying the need for using dates as text.

                    Although you probably have found a solution by using the number interpretation of the date, it seems to me that an adaption of the custom function to actually return valid dates no matter the locale should be considered, at least if you plan to use the function again in another scenario.


                    Anyway, that's just my 2-cents :)




                    • 7. Re: Passing dates as numbers

                      Hi Andreas

                      When you say "the custom function to actually return valid dates no matter the locale" - which CF are you referring to?

                      • 8. Re: Passing dates as numbers



                        I was referring to the "Property Lists" custom functions that you were using, suggesting finding a way to make it return valid dates instead of text variable, maybe using a global date field.


                        I have not looked at these functions specifically, this is more a general comment on trying to avoid dates as text as it opens up a whole can of worms.

                        If you must do it for say, integration purposes, use the calc engine to assemble the date in the expected locale.


                        Lastly, always try to leave date/time/numbers format at their general settings, as any "forced" formatting in these dialogs will override the system settings.