8 Replies Latest reply on Dec 14, 2011 10:36 PM by CarstenLevin

    Dates Set as d/m/y are converted to m/d/y

    Malcolm

      Can anyone tell me what to do about my date problem?

       

      I have a calculation field that produces a date result. When I ask for the content of the field using the Data Viewer I see the date in the local format D/M/Y. However, when I pass the field content as a script parameter it is passed in US format M/D/Y.

       

      In the Data Viewer I get these results:

       

      dateStart: 30/6/2011

      GetAsDate( dateStart ) : 30/6/2011

      GetAsText( dateStart ) : 6/30/2011

       

      To test the value I can convert to a number and pass that to a date function

       

      Month( GetAsNumber(Reports::Date Start) ): 6

       

      To me that suggests that the date is being correctly stored so why does the conversion to text create this result:

       

      GetAsText( GetAsDate(GetAsNumber(Reports::Date Start)) ): 6/30/2011

       

      I became aware of the problem when I started to script a search on the date field. The script was throwing an error because the field would not accept the calculated string for the date range.

       

       

      I'm using FMA v11.03 on Mac OSX 10.6.8

      The application is running on FMS 11.0.3.309 on Windows Server 2008

       

       

      Malcolm

        • 1. Re: Dates Set as d/m/y are converted to m/d/y
          Bobster

          Hi Malcolm,

           

          I struck a similar problem a couple of times when my client's IT guy repeatedly reinstalled Windows and left Date Display in the default US format.

           

          It wouldn't be something as simple as that causing the problem, would it?

           

          Bob.

           

          Bob Stuart

          Lord Of The Files

          Think Data Pty Ltd

          Noosa

          Queensland

          Australia.

           

          +61 7 5442 5624

          +61 413 350 993

          • 2. Re: Dates Set as d/m/y are converted to m/d/y
            Malcolm

            Comment to SysAdmins: This new system is batty. My replies are F***D UP by the system, however, I never know because I am not included in the response. This reply was empty.

             

            When I replied I said

            "I've just looked at the server and the regional settings are for Australian defaults: dd/mm/yyyy

             

            Regardless of that setting. The behaviour should be set by the file and by the user's machine settings, shouldn't they?"

             

            Malcolm

             

            Message was edited by: Malcolm

            • 3. Re: Dates Set as d/m/y are converted to m/d/y

              Hi Malcolm,

               

              I am sorry this site is messing you up; it can be frustrating but I believe they are working on it.

               

              As for your date issue, be sure your file was created under same settings as your current system formats by this method (for those who may not know):

               

              Save a copy of the file as clone (no records), then import the data into the copy (don't forget to reset  serial numbers, etc.). The clone will inherit the current system formats. (quote by Comment)

               

              But you probably already know this so I suspect it might be a reported 'text date' issue.  There have been many posts about it (at least 7 that I have read), some involving // , some involving Get(CurrentDate) ...  here is the only link I can find:

               

              http://forums.filemaker.com/posts/7864533aa8

               

              Basically, it says that using Get(CurrentDate) to place a date in a text field inserts a date with the file's original system settings which may be different from the current settings. (US MMDDYYYY format will persist in the field even after European date settings are selected for the file.

               

              It has bitten people occasionally when using text dates.  I am sorry that I cannot provide more links than this one right now; I shall search again later when I have a moment.

               

              Message was edited by: LaRetta BTW, FMI responds on that link indicating that they are aware of the issue.  At least two other posts in that forum were also responded to by FMI on the text date issue.

              • 4. Re: Dates Set as d/m/y are converted to m/d/y
                Stephen Huston

                Hi Malcolm,

                 

                As long as the field itself is defined as a "Date" field in the table, it is storing the same numeric value no matter how it is being displayed. That's why FileMaker can extract the correct Day, Month, or Year via the calculation engine or even display it via custom layout formatting of the field.

                 

                The other responses above explain the various issues with the default date/time settings of the OS -- a whole other issue tha, the stored date data itself.

                 

                Stephen Huston

                • 5. Re: Dates Set as d/m/y are converted to m/d/y

                  Here is a better link

                   

                  http://forums.filemaker.com/posts/fc3dd2d7a8?lang=en_US

                   

                  This one discusses how 11.0v4 (Mac) provides a fix for Lion

                   

                  http://forums.filemaker.com/posts/57c04dae9c?lang=en_US   not related to this issue

                   

                  Not sure if this text date bug is related to your issue but it just might be displaying the inconsistent results you see in the data viewer.

                   

                  Message was edited by: LaRetta Sorry these links aren't that much help after all.  I was rushing a bit.  I'll find better ones for you.

                  • 6. Re: Dates Set as d/m/y are converted to m/d/y
                    Malcolm

                    It seems like I'll have to clone the file and re-import. That is boring.

                     

                    It is hard for me to believe that the file was originally created with US date settings. All work has been done on Macs with Australian settings or on the server which is Windows Server 2003 with location settings set to Australia. I'll have to check with my colleague to be absolutely sure because he created the file but he is also lives and works in Australia so I can't imagine him ever having a machine with US settings.

                     

                    If I'm right and the file was created with Australian settings then it is possible for a file to acquire this date bug simply by backups, copying and cloning.

                     

                    The difficulty for us is that it is very hard to work around. I noticed this because I was using a date that caused an error (30th June). Any date up to the twelfth will happily switch Month for Day. The only certain system I can think of would be to decompose the date components and construct a text string from them. A custom function could do that but there is a lot of work involved in maintaining it (ie, remembering).

                     

                    Malcolm

                    • 7. Re: Dates Set as d/m/y are converted to m/d/y

                      Hi Malcolm,

                       

                      Another possible cause of the US Date format is that the file in its original and rudimentary state was sourced from a M/D/Y region. When first opened you would get a dialog asking if you want to keep the file's settings or use local settings. The default response for that dialog anticipated the new user would retain the original system formats. Some years ago the default response was changed to always use the local formats when opening a file that had originated with the M/D/Y formats.

                       

                      Is it possible that the original developer overlooked the need to use Local Settings and this is the first time you've needed to have them diplayed in the local format?

                       

                      I'm not sure that this is a credible explanation but offer it as a possible way of helping to diagnose the cause.

                       

                      Cheers,

                       

                      John

                      Hamilton, NZ

                      • 8. Re: Dates Set as d/m/y are converted to m/d/y
                        CarstenLevin

                        Yes, FileMaker always store the date in a date field as a number (integer). Not as a date, and therefore is not harmed by different date formats.

                        The date is calculated from the start of our calendar (nearly 2011 years ago ... whish I could write this as a calculation here to keep it correct:-)

                         

                        dateinteger.jpg

                        The problem is that the our American Friends have decided to display dates in a very very peculiar way*. Instead of showing today as 2001/12/15 or 15/12/2012 they are mixing the different parts of the unit date.

                         

                        The default display of a date is determined by FileMaker when entering the first value in a new file. Or when entering the first value in a new clone of a file.

                        So if your system is messing up your dates you should probably save a clone of each file ON A SYSTEM SET TO YOUR REGIONAL SETTINGS (windows) or WITH YOUR COUNTRY AT THE TOP IN THE INTERNATIONAL SETTINGS (mac). Then open them, enter a date, check that everything with dates calculates as you want. And then import your data (remember serial counters [killers] etc.).

                         

                        *Sorry for this