13 Replies Latest reply on Jul 30, 2017 9:27 AM by philmodjunk

    Need Help Formatting Date/Time


      I need some guidance in formatting a field that contains date & time into something FileMaker can sort.  The data is from an existing MYSQL DB.  The incoming field looks like this "31/7/2017 16:30:00" (DD/M/YYYY HH:MM:SS or DD/MM/YYYY HH:MM:SS).


      I would like to be able to query on today's date and sort by date/time.  When I try to do that as it is currently formatted coming from the MYSQL DB it sorts using the DD.  I have it set as a timestamp.


      I'm thinking that I will need to use a calculation to reformat the data into something Filemaker could use.  I'm not to great at calculations.  I've spent a good portion of my day researching online and referring to books trying to figure one out, but nothing is working.  There is a couple of things I don't understand.


      1.  Does the time need to be in a separate field from the date?

      2.  When I put a calculation into auto-enter when defining a field, what triggers it to change the format for existing data?

      3.  I need assistance with the calculation itself.


      Thanks in advance for any help with this.

        • 1. Re: Need Help Formatting Date/Time

          I'm pretty sure that if you google filemaker custom functions you will find a calc that will work.


          timestamp field stores date and time info


          calculations are refreshed when the definition is changed or any fields referenced in the calc are changed

          • 2. Re: Need Help Formatting Date/Time

            There are text functions that can extract each chunk of info from this string. There are date and time functions that can take those chunks and return a date and a time plus a timestamp function that can take the date and time and return a time stamp.


            A custom function can use the above functions to do this for you or you can use them in your own calculation.

            • 3. Re: Need Help Formatting Date/Time

              A bit of help to get you on your way:


              1.  You'll have to parse the time into a separate field.  You might want to do that in your MySQL database before importing.


              2.  When you refer to auto-enter, I assume you mean the setup of a field under [FMP's menu bar] manage database > auto-enter.  This can be used to automatically enter the time or date in a field on either creation of a record or modification of a record, as you choose.  You don't want set auto-enter to work when you're importing, obviously.  One strategy would be to turn off auto-enter, import your data, and then turn on auto-enter for when you create new records from within FMP.


              3.  In FMP lingo, formatting means how the data from a date or time field appears on a layout, not how data is stored in a record.  Formatting is handled elsewhere in FMP with the layout tools.  Use layout mode, then look at View > Inspector for the tool to specify the format (i.e. appearance) of a date or time field on a layout.


              4.  Once you've parsed the time and date into separate fields, and done your import, then sorting is simply a matter of creating a FMP layout, putting the time and date fields on it, and setting up a sort.  The records > sort records command from the menu bar is pretty self-explanatory, or you can refer to the FMP documentation for details on sorting.

              • 4. Re: Need Help Formatting Date/Time

                Are you getting the MySQL Timestamp using ODBC or how exactly?


                Confusion 101: FileMaker's date and times are not consistent. I've had cases where I had three different date and time formats in FMP depending on what I was doing (ExecuteSQL, etc.). I've spent many hours working around FMP's inconsistent date formats. And, as important as formatting dates is, FMP's date formatting functions leave lots to be desired.


                Possible Approach 1: Try to take the MySQL date and figure out what pieces need to be in what places so that you can make it a FMP Timestamp (or date or whatever). Then, check out some functions like Position(), LEFT(), MIDDLE(),

                and RIGHT() and you can chop up and reassemble the MySQL date so it will work in FMP.


                Possible Approach 2:

                Or, in MySQL, you could also create another field where you can manipulate the MySQL date into a date FMP will like using the much more powerful date time functions in MySQL. Then, just bring that field into FMP however you're using MySQL data from FMP.


                MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions

                • 5. Re: Need Help Formatting Date/Time



                  To get a TimeStamp you can use the following:

                  Timestamp ( Date(

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); "/"; ¶); 2 );

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); "/"; ¶); 1 );

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); "/"; ¶); 3 ));


                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); ":"; ¶); 2);

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); ":"; ¶); 3);

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); ":"; ¶); 4)))


                  The result is: 7/31/2017 4:30 PM


                  To get a Text  you can use the following:

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); "/"; ¶); 2 ) & "/" &

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); "/"; ¶); 1 ) & "/" &

                  GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); "/"; ¶); 3 ) & " " &

                  GetAsTime (GetValue ( Substitute (Substitute ( MySQL Import; " " ; ¶); "/"; ¶); 4 ))


                  The result is: 7/31/2017 16:30:00


                  See sample file.

                  Hope this helps.





                  • 6. Re: Need Help Formatting Date/Time

                    Thank you everyone for your assistance.  You have helped tremendously. 


                    I don't have any control over the MySQL database as it's over 20 years old and our developer doesn't want to make any changes because he doesn't know what would break if he did.


                    The solution Karina provided is what will work best.


                    Thanks again!

                    • 7. Re: Need Help Formatting Date/Time

                      If we all assume dates are important, I believe it would be wonderful if:


                      1. FMI would just adopt the ISO standard for DateTime so clever code, and the HOURS creating that clever date conversion code, is just not necessary.


                      2. Moreover, it would be wonderful, if FMI would have a consistent date format within FMP itself. ExecuteSQL, for example, returns a date FMP can't handle in its own functions!


                      3. Finally, for those who need additional control over date formats, there should be simple functions to convert from one format to another so hours of clever coding to work around product omissions aren't necessary.

                      • 8. Re: Need Help Formatting Date/Time

                        "and the HOURS creating that clever date conversion code, is just not necessary."


                        Sorry but I don't see why you find this so hard. FileMaker has exactly one INTERNAL storage method for dates, time and time: an integer recording days (date) or seconds (time and time stamps) from an arbitrary starting point (year 0 for dates and time stamps, midnight for time. )


                        The text based representation of those dates can take any number of formats, some are specified by a software standard such as that for SQL, others due to regional preferences such as MMDDYYYY vs DDMMYYYY and some are simply user preference.


                        None of this is unique to FileMaker and computing a date from one text based date representation or another is at most a matter of a few minutes of coding easily handled by either a built in or custom function. This takes minutes not hours.

                        • 9. Re: Need Help Formatting Date/Time


                          The problem (that should have been fixed long ago) is easily demonstrated. This issue should be abstracted (handled) by FMP, not by the poor developer looking around for some code to fix this.


                          Example 1:


                          1. The screenshot below shows a script step SET FIELD and the existing FMP Timestamp field (BEFORE the script  runs):




                          2. Then, after running the script, we see that FileMaker cannot process (store) it's own date formats:



                          NOTE: There is only one record in the "Untitled" table, so the SQL returns what should be a valid Timestamp FMP can understand.

                          There is no reason I can think of (in a multi-hundred dollar product) why FMP should not be handling this ALL-FILEMAKER code.


                          Sure, I have a script that "handles" this situation, but the situation shouldn't occur in the first place. ExecuteSQL and its date formats have now been around for five FMP versions. Abstraction means simplicity. And, FMP should be handling these obvious date chores without the developer having to write scripts.




                          Example 2: Date Math


                          I have run into the same type problems using Date Math with the inconsistent FMP datetime formats as FMP has problems there as well. I can certainly give you an example of those, too, as well, if you like.




                          I also found that you can't convert a Timestamp into a Date in SQL as there is no function for that making working with FMP's inconsistent date formats even more of a challenge.

                          • 10. Re: Need Help Formatting Date/Time

                            Using about three lines of code in a microservice, I easily handled two user goals:


                            1. Convert ExecuteSQL Timestamps stored as TEXT to a Timestamp FMP understands. The Example 1 below shows on the left a Timestamp returned by ExecuteSQL. After clicking "Convert", you see on the right the value FileMaker can understand in its own Timestamp field type.


                            2. Dynamically run ExecuteSQL statement using the SQL statement in the "Sql" field below and store a Timestamp FMP understands. Example 2 below takes the SQL statement and executes it in an INSERT FROM URL script step after clicking the "Call SQL" button. That script calls the microservice, passing the FileMaker ExecuteSQL formatted Timestamp. Again, the microservice formats and returns the value FileMaker can understand in its own Timestamp field type.


                            (Should this effort be required to accomplish typical user goals? IMHO, No.)




                            (CLICK ON IMAGE IF NOT SHOWN ON RIGHT)

                            So, using this code, you could update a few or a few thousand (or more) eSQL Timestamps stored as TEXT or get a return value FMP can understand and store as a Timestamp.


                            This code could also be used to do simple date math using eSQL Timestamps.


                            HOPE THIS HELPS.

                            • 11. Re: Need Help Formatting Date/Time

                              This is exactly what I was talking about. Your SQL result is not a date nor a timestamp. It is a text representation of the original timestamp value. Keep in mind that this function could be returning a block of text with data from multiple records, multiple fields that in turn have multiple data types.


                              What I questioned earlier was not that such text to date, time, or timestamp conversions are needed nor your opinion that it shouldn't be necessary, but that it took "hours of clever code" to do. I can take the result of your timestamp example query and use a custom function to parse the text back into a TS very quickly, creating or finding/downloading the CF takes minutes. Using it in my solution from that point on takes seconds.

                              • 12. Re: Need Help Formatting Date/Time

                                Not sure if you're replying to me, but my service takes a ExecuteSQL-formatted TS and returns a FMP-accepted-understood TS.  In the multiple examples I did offline, that TS field sorts correctly with the return values.


                                My real argument isn't that you can't do this conversion in a CF. I have too in the past.


                                Instead, we just need more robust date conversion functions.

                                • 13. Re: Need Help Formatting Date/Time

                                  With regards to "math" I have never encountered "inconsistencies" as long as I work with the values as numbers, not as text.


                                  You can often leverage that number basis to simplify format issues when you need to display that value as text.


                                  Not only can you exploit this in ExecuteSQL, by using optional parameters, I saw a clever example of this at DEVCON where a timestamp was set as a JSON element with JSONnumber declared as the type. This made it very easy to update a timestamp field on the receiving side.


                                  Note that many other applications also use the same arbitrary number method for such values. In such cases, this often is handled simply by adding/subtracting an offset number.