14 Replies Latest reply on Jan 2, 2017 4:55 PM by user19752

    Date to Text?

    jreed

      I use dates converted to text in several Tables. The dates are part of a text string used in different relationships.The dates are converted using the GetAsText (data).  There are several Tables that use the date in a string as a relationship. The 1st of January converted the date in two different ways.

      I some Tables the date was converted as "01/01/2017" in others it is converted as "1/1/2017".

       

      I am running Filemaker Server 14 on a 2012 Server.

       

      What causes the date to convert differently?

        • 1. Re: Date to Text?
          David Moyer

          Hi,

          it looks like it's based on how the date was entered.

          When I test it, GetAsText ( 01/01/2017 ) yields "01/01/2017" and GetAsText ( 1/1/2017 ) yields "1/1/2017" and GetAsText ( 1/01/2017 ) yields "1/01/2017".

          • 2. Re: Date to Text?
            jreed

            Thanks, I had not thought of that.

            • 3. Re: Date to Text?
              David Moyer

              a bit more - this is a machine-specific setting.  In Windows, I can change the date format in Control Panel - Clock, Language, and Region.  For example, this changes the format of FM's ctrl-hyphen ... insert current date.

              • 4. Re: Date to Text?
                jreed

                Most of the dates are set automatically in Filemaker using Date Created. A few are set using the poop calendar. and a limited number are actual manual inputs. As I see the different formats show up I am calculating the date to text in the field calculation. I had thought the GetAsText (data) function would convert consistently but see that it actually converts what is entered.  This just one more lesson learned.

                • 5. Re: Date to Text?
                  jreed

                  Pop up calendars vs "poop calendars"

                  • 6. Re: Date to Text?
                    Malcolm

                    That is exactly what is happening. FileMaker preserves the text string that the user entered.

                     

                    If you want to normalise the value I suggest you use GetAsNumber or use a custom function to return the date in ISO format, 2016-05-23.

                     

                    malcolm

                    • 7. Re: Date to Text?
                      siplus

                      Dates are internally stored as numbers, best thing you can do is GetAsNumber(yourdatefield).

                      • 8. Re: Date to Text?
                        Malcolm

                        siplus wrote:

                         

                        Dates are internally stored as numbers, best thing you can do is GetAsNumber(yourdatefield).

                        No, that is not correct. The dates are stored exactly as they are entered. This information was first given to us at one of the FileMaker Training Sessions run by David Head. As David Moyer's experiment showed, the field stores and returns the original input.

                         

                        Malcolm

                        • 9. Re: Date to Text?
                          David Moyer

                          this s more interesting than I thought.  Given:

                          GetAsDate(

                            GetAsNumber(

                              GetAsDate("01/01/2017")

                            )

                          )

                          After a brief test, the above will yield whatever your machine is set to format ... 1/1/17; 01/01/2017, 01/01/17, etc.

                          • 10. Re: Date to Text?
                            Malcolm

                            Working with text strings is different to working with stored data in a date field. You can give your formula different text strings, e.g., "5/12/2016"; "5-12-16", in the data viewer and there is no need to convert to number and back to a date, you'll get the same result, no?

                             

                            Put those different formats into a date field and when you say GetAsDate or GetAsText that the formatting is respected in the output.  You'll see what you entered. Converting those dates to a number effectively strips the formatting information and when a date is created from the number the system defaults are used.

                             

                            malcolm

                            • 11. Re: Date to Text?
                              siplus

                              "FileMaker Pro stores dates as the number of days since January 1, 0001"

                               

                              from

                               

                              FileMaker Pro 15 Help

                               

                              maybe you are right and Filemaker is incorrect.

                               

                              Though, it's still called Filemaker and not Davidmaker, so I don't care how many Davids express a original opinion, for me what counts is the official position.

                              • 12. Re: Date to Text?
                                David Moyer

                                I'm a bit confused ... FM definitely stores dates as an integer.  Excuse me for being argumentative or pedantic ...

                                 

                                FileMaker Pro reads a date value as an integer (whole number) representing the number of days elapsed from 1/1/0001 up to the specified date - FTS

                                From the Data Viewer:

                                Capture.PNG

                                 

                                • 13. Re: Date to Text?
                                  Malcolm

                                  With respect, you have simply showed that the GetAsNumber function is able to generate a number from a date. You haven't shown us anything about the storage of dates.

                                   

                                  Your earlier experiment with GetAsText is more meaningful because the text which is entered is being preserved. If the text was converted to a number prior to being stored, we wouldn't be able to recover the text formatting.

                                   

                                  malcolm

                                  • 14. Re: Date to Text?
                                    user19752

                                    There are two "storage" for date field, entered text (need for field formatting option etc.) and date value (as number) for index.

                                    I guess there are other storage that keep year/month/day/dayofweek, since wild card searching did it very fast.

                                    1 of 1 people found this helpful