1 2 Previous Next 18 Replies Latest reply on Aug 28, 2012 8:18 PM by normancole

    I need help with a date formatting issue..

    normancole

      Hello Folks...

       

      Dates formatted as 12/08/2012 show up as 12-08-2001 when imported into another file. We can, of course, reformat the dates to show the slashes again, but we also need to display the dates (occasionally) as text. Using the "Get as text (data)" function returns the date with the dashes..and we need to have the slashes in text format for a number of reasons...

       

      The same function used on the dates in the original file return the date with slashes just fine.

       

      What's going wrong here...?

       

      Cheers,

       

       

      Norm

        • 1. Re: I need help with a date formatting issue..
          psijmons

          In the other file, set the date field to display the way you want it using the Inspector.

          Under the hood, dates are saved as number of days since 01-01-0000, the display is up to the way you configure it in the field via inspector but also depends on system settings on the machine this file was opened the first time as a non-hosted file. Also a script step Use System Formats On/Off may have effect.

          • 2. Re: I need help with a date formatting issue..
            comment

            normancole wrote:

             

            Dates formatted as 12/08/2012 show up as 12-08-2001 when imported into another file.

             

            What do you see when you click into the field?

            • 3. Re: I need help with a date formatting issue..
              comment

              psijmons wrote:

               

              Under the hood, dates are saved as number of days since 01-01-0000

               

              Actually. dates are saved as entered (and the epoch year is 0001).

              • 4. Re: I need help with a date formatting issue..
                normancole

                The date looks OK (MM/DD/YYYY) when I clicked on it, and I was able to set this format using the inspector. However, using this date in a calculation that requires it to be shown as text (using the "GetAsText(data)" function) returns the date as YYYY-MM-DD. Since it is a text field now, I'm not able to reformat it using the inspector.

                 

                I created a new F/M database with a table containing a date field and another field for the calculated date as text and then imported the date from the original file. It worked just fine, The calculated text date comes out at MM/DD/YYYY.

                 

                I'm puzzeled that the process works OK for the new data base but not for the other. Is there a possibility that the first file I'm importing into (Call it file "A") is corrupted ? I certainly hope not.

                 

                I created a new test table in File A with the two fields and imported the date again. No luck - the text date is still YYYY-MM-DD.

                 

                In the import script, I've tried the import with the "Set Use System Formats turned On and Off, with no effect.

                 

                Either I'm not doing something very simple here, or ...??

                 

                Hope you can help..

                 

                 

                Norm

                • 5. Re: I need help with a date formatting issue..
                  comment

                  normancole wrote:

                   

                  Either I'm not doing something very simple here, or ...??

                   

                  ... or you are complicating it more than it already is.

                   

                  How the field is formatted to display on a layout has nothing to do with the issue. What matters (regarding the date field) is how the date was entered. If, when you click into the field, you see the date as MM/DD/YYYY, then that's how it was entered - and that's how it will be exported (unless you choose a formatted export). Not really relevant here, since you are importing, not exporting.

                   

                  The other thing that matters is the date format being used by the importing file. If you want to keep using the MM/DD/YYYY format, then make sure your operating system's short date format is set to MM/DD/YYYY, and only then create (or clone) the importing file.

                   

                  The import can be performed directly from a date field into a date field. The conversion to text is only distracting in this context.

                  • 6. Re: I need help with a date formatting issue..

                    Well,

                     

                    Most of what has been said or suggested here is wrong.

                     

                    The problem and its solution has been described at <http://fmdiff.com/fm/locale.html>.

                     

                    Winfried

                    • 7. Re: I need help with a date formatting issue..
                      comment

                      If you are saying that what I said was wrong, I suggest you be more specific.

                      • 8. Re: I need help with a date formatting issue..

                        Michael,

                         

                        No offense meant.

                         

                        It does NOT matter how a date is entered. If it is a date field you may enter only legal dates anyway. There is a chance a wrongly entered date slips through by appearing legal, but being interpreted as a date different to what was intended.

                         

                        It does NOT matter what the date "looks like" when imported from a date field. The import takes place in the internal format, which is the number of days since January 1st of year 1. Test for yourself within DataViewer with

                        GetAsNumber (Date (1; 1; 1))

                        or

                        GetAsDate (734739).

                         

                        The text format of the date you now see within DataViewer (no files open) matches your system settings. These system settings will be inherited by any newly created file - and - by any clone that is opened on this system for the first time after cloning. This also corresponds to the format you see when "you click into a field".

                         

                        Exporting a date field is always formatted as text which - with no surprise - appears exactly as described above.

                         

                        So the correct and permanent solution to the problem is

                        - set your system to the desired locale settings

                        - clone your original file

                        - import the data into the new clone

                         

                        Winfried

                        • 9. Re: I need help with a date formatting issue..
                          comment

                          Winfried Huslik wrote:

                           

                          So the correct and permanent solution to the problem is

                          - set your system to the desired locale settings

                          - clone your original file

                          - import the data into the new clone

                           

                          Isn't this exactly what I suggested?

                           

                          I suggest you edit your earlier message, because even if no offense is meant, it is still offensive. What's even worse, nothing can be learned from it.

                          • 10. Re: I need help with a date formatting issue..
                            comment

                            BTW, you are wrong about this:

                             

                            Winfried Huslik wrote:

                             

                            The import takes place in the internal format, which is the number of days since January 1st of year 1.

                             

                            You can test this by creating a source file using the M-D-YYYY format and entering the following entries:

                             

                            1-1-2012

                            01-1-2012

                            1-01-2012

                            01-01-2012

                            1/1/2012

                            01/1/2012

                            1/1/2012

                            01/01/2012

                             

                            These are all valid entries for the given format. Now clone your file and import the records. If your assertion were correct, then all entries in the target file would be "1-1-2012". In fact, all the entries are imported exactly the way they were entered into the source file.

                            • 11. Re: I need help with a date formatting issue..
                              normancole

                              Thanks for the advice, and my apologiies for being somewhat obtuse on this issue.

                               

                              To make sure I have this correctly, the root of the problem is that somehow the importing file was initially created with system settings that do not match the current settings. I can import a date from elsewhere just fine, but when changing the date to text, the application translates the date according to the "old" settings. (YYYY/MM/DD) (Even when the format of the imported date is correct (DD/MM/YYYY).

                               

                              The new file consolidates over fifty files from an old (v5.5) database and the thought of having to clone this file and re-import the data to deal with this issue is somewhat daunting, to say the least !

                               

                              Is there no other way to deal with this ?  I'm surprised that F/M does not have some capability to change the default settings for dates without having to go through what appears to me to be a very big process..

                               

                              Is there anyother thing we could do ?

                               

                              Cheers,

                               

                              Norm

                              • 12. Re: I need help with a date formatting issue..

                                Michael,

                                 

                                I usually tend not to be wrong with my statements.

                                 

                                If I were wrong it would not be possible to convert a date from the US to Great Britain, Australia, Germany, or Japan without messing the date, BUT IT IS possible while maintaining the correct date (ignoring the format).

                                 

                                You example does not prove anything as legal stays legal. What I meant was something like

                                1-1-12

                                1-12-1

                                12-1-1

                                which all "look like" being legal, but may be interpreted differently with different locale settings.

                                 

                                And I said "no file open".

                                 

                                Many wrong assumptions about the date format come from the fact that it rarely changes while staying within the same country.

                                 

                                Believe me, I've been at all the bytes and bits of every format within FileMaker.

                                 

                                Winfried

                                (offline for the next 3 hours - or so)

                                • 13. Re: I need help with a date formatting issue..
                                  comment

                                  normancole wrote:

                                   

                                  To make sure I have this correctly, the root of the problem is that somehow the importing file was initially created with system settings that do not match the current settings.

                                   

                                  Indeed, this seems to be the source of the problem.

                                   

                                   

                                  normancole wrote:

                                   

                                  Is there no other way to deal with this ?  I'm surprised that F/M does not have some capability to change the default settings for dates without having to go through what appears to me to be a very big process..

                                   

                                  Well, they say that there is another way: set your file to 'Always use current system settings' in File Options. This works reasonably well. but still has a few quirks. That's why the best course of action is to clone the file and reimport the data.

                                  • 14. Re: I need help with a date formatting issue..
                                    comment

                                    Winfried Huslik wrote:

                                     

                                    If I were wrong it would not be possible to convert a date from the US to Great Britain, Australia, Germany, or Japan without messing the date,

                                     

                                    No, that doesn't follow at all. The correct conclusion is that the conversion is done during import, and the process combines 3 inputs:

                                     

                                    1. The date "as entered" in the source file;

                                    2. The format used by the source file;

                                    3. The format used by the target file.

                                     

                                    The conversion takes places only when the two formats do not match, otherwise the date is preserved "as entered" in the source file.

                                     

                                    In other words, a file with an entry of Aug 24, 2012 does not contain the value of 734739 anywhere "at all the bytes and bits" of the file.

                                     

                                     

                                    Winfried Huslik wrote:

                                     

                                    And I said "no file open".

                                     

                                    Makes no difference.

                                     

                                     

                                    Winfried Huslik wrote:

                                     

                                    I usually tend not to be wrong with my statements.

                                     

                                    Well, I am flattered you made an exception for my sake...

                                    1 2 Previous Next