1 2 Previous Next 19 Replies Latest reply on Jun 17, 2014 10:54 AM by gdurniak

    Formating SQL Dates to US format

    mark_b

      Hi All,

       

      I have checked the archives and did not find an answer to formatting SQL dates (more than one date returned) from YYYY/MM/DD to MM/DD/YY format. Here is my SQL statment which lies in a ToolTip calculation:

       

      Let (

      ~SQL = "SELECT DISTINCT dateCreated

      FROM CoursesChanged

      WHERE id_Course = ?";

       

      ExecuteSQL ( ~SQL; ""; "¶"; Course via Dashboard id_Program::id)

      )

       

      And here is what is being returned:

      2014-06-02

      2014-06-04

       

      There will usually be more than 2 rows returned so I can't use the Substitute (SQLResult; "-"; ¶) since the result will be more than one date. I hope there is an easy solution.

       

      Cheers, Mark

        • 1. Re: Formating SQL Dates to US format
          DavidZakary

          Use the ValueCount function to figure out how many are returned, pluck out the one you need (or loop through them) and use a custom function (or favorite method) to reformat the dates as required.

           

          You then build a CR delimited list of the dates in the format you need.

          • 2. Re: Formating SQL Dates to US format
            Mike_Mitchell

            Building on David's suggestion, here's a CF that accomplishes the task:

             

            //  Function name: cfUnixDateToDate ( input )

            // Purpose: To convert a Unix-formatted date text string (such as returned by ExecuteSQL()) to an actual date value

            // Author: Mike Mitchell, Net Caster Solutions, www.netcastersolutions.com

            // Original publication date: 4/25/2013

             

            Let ( [

            inputNumeric = Filter ( input ; "0123456789" ) ;

            inputYear = Left ( inputNumeric ; 4 ) ;

            inputMonth = Middle ( inputNumeric ; 5 ; 2 ) ;

            inputDay = Right ( inputNumeric ; 2 )

            ] ;

             

            Case ( Length ( inputNumeric ) = 8 ;

             

                Date ( inputMonth ; inputDay ; inputYear ) ;

             

              ""

             

            )

            )

             

            HTH


            Mike

            • 3. Re: Formating SQL Dates to US format
              user19752

              Formatting in SQL

              SELECT DISTINCT RIGHT(STRVAL(100+MONTH(dateCreated)),2)+'/'+RIGHT(STRVAL(100+DAY(dateCreated)),2)+'/'+STRVAL(YEAR(dateCreated))

               

              very long expression, but not so slow (about x2 ?)

               

              a bit shorter

              SELECT DISTINCT SUBSTR(STRVAL(dateCreated),6,2)+'/'+RIGHT(STRVAL(dateCreated),2)+'/'+LEFT(STRVAL(dateCreated),4)

              • 4. Re: Formating SQL Dates to US format
                taylorsharpe

                I often leave SQL formatting as is in the SQL report, but when I have a FileMaker field that I need to insert the SQL date, I set the FileMaker date field to Auto-Enter with the following calculation:

                 

                Let ( [

                     F1 = Table::DateFieldName ;

                     F2 = GetAsDate ( F1 ) ;

                     F3 = Substitute ( F1 ; "-" ; ¶ ) ;

                     F4 = Date ( GetValue ( F3 ; 2 ) ; GetValue ( F3 ; 3 ) ; GetValue ( F3 ; 1 ) ) ;

                     F5 = If ( F2 = "?" ; F4 ; F2 )

                ] ;

                 

                F5

                 

                )

                 

                 

                 

                With this formula, if what you put in the date field is invalid, it then tries format it assuming it is in the SQL format and automatically converts it to the FileMaker format. 

                • 5. Re: Formating SQL Dates to US format
                  mark_b

                  Thanks All,

                  I've been away from the computer for a day.  I used user19752's answer because it worked within my tooltip.  The date field is a system entered creation date so it won't be blank or have an invalid value. But there will be many records, thus the "distinct" modifier. I'm liking ExecuteSQL more and more as I learn SQL.  Thanks again for the help.

                  Cheers, Mark

                  • 6. Re: Formating SQL Dates to US format
                    taylorsharpe

                    Since ExecuteSQL came out, there are few tables and scripts that I make that don't rely on SQL anymore.  I am really happy FileMaker added this feature.  The disadvantage is that fields are not dynamically linked to the FileMaker schema and will break if you do something like change a field name.  Actually, now that I am hooked on SQL for FIleMaker... I keep finding myself wanting more like UPDATE, INSERT, DELETE, etc. 

                    • 7. Re: Formating SQL Dates to US format
                      BruceRobertson

                      Scripts I understand.

                       

                      Tables? Don't we still have some significant performance issues?

                      Maybe a little more detail about how you use the feature with tables?

                      Auto-enter calcs, I am guessing.

                      • 8. Re: Formating SQL Dates to US format
                        taylorsharpe

                        One of the big misconceptions many of us had, including me, was that SQL would be faster.  SQL is just another interface for connecting to FileMaker and it does not in general improve performance.  There are a few situations that due to how it access the FileMaker engine, it does go faster, but there are also times where it goes slower than traditional FileMaker processes. 

                         

                        I do use SQL in FileMaker calculations and it works just as well as normal FileMaker calcluations and I see no reason not to fully embrace it.  It has the same performance penalty issues as other unstored calculations and in general, unstored calcs are best avoided, but sometimes needed. 

                         

                        Where I have found SQL the biggest improvement is in global variable reporting tables where no data is stored, it is all stored in a global variable.  This in combination with Perform on Server has really increased the performance of some reports for my clients.  The disadvantage is that it is not good for editing results.  So for just reporting (or graphing), it is great. 

                        • 9. Re: Formating SQL Dates to US format
                          beverly

                          Mark, lots of workarounds (and maybe what you want). I just wanted to remind you that you can have SQL make the "conversion" (and not complex as user19752 did - but the leading 0's are included in that calc!):

                           

                          1. John Renfrew discovered that if you concatenate the date with "nothing", that if becomes the standard date format that we use (MM/DD/YYYY or DD/MM/YYYY or our non-Am cousins):

                           

                               SELECT ''||dateCreated // note that's TWO single quotes before the two pipe characters

                           

                          2. STRVAL() will convert to "text":

                           

                               SELECT STRVAL(dateCreated) // again depending on where you are located in the world you get MM/DD/YYYY or DD/MM/YYYY

                           

                          3. COALESCE() is a function that does several things. For dates:

                           

                               SELECT COALESCE(dateCreated,'') // note the TWO single quotes after the comma

                           

                          So using any of these should give your the column you want.  I prefer the STRVAL() function.

                           

                          Beverly

                          • 10. Re: Formating SQL Dates to US format
                            taylorsharpe

                            Wow... Beverly!  I didn't know about those.  Already starting to change some scripts and use the STRVAL function.  Thanks!

                            • 11. Re: Formating SQL Dates to US format
                              Mike_Mitchell

                              Indeed. I completely forgot about the concat, even though I use it. How awful is that?  

                               

                              Thanks, Beverly.

                              • 12. Re: Formating SQL Dates to US format
                                mark_b

                                Beverly, Thanks for the SQL tricks.  Have you given any thought to expanding your "The Missing FM 12 ExecuteSQL Reference" to include more of these tips, tricks and workarounds? I'm reluctant to use any of the regular SQL books because I'm not sure if their syntax and explanations will work with FMP.  And, I suppose that there is no guarantee that these tricks won't go away with a new version of FMP, unless they are really not tricks.

                                Cheers, Mark

                                • 13. Re: Formating SQL Dates to US format
                                  beverly

                                  I've gotten a few new tricks and there are some additional ExecuteSQL functions in FMP13. Uh, maybe?

                                   

                                  Beverly

                                  • 14. Re: Formating SQL Dates to US format
                                    raycon

                                    Come on Beverly, please?  I'm a newbie who Taylor has recently hooked on SQL and I would love to see what else I can do with it?

                                     

                                    Ray

                                    1 2 Previous Next