8 Replies Latest reply on Jan 22, 2013 7:38 AM by Gingernut

    Reformatting the results of a SQL date

    Gingernut

      I have a chart which shows number of toners invoiced to a customer relating to their photocopier.

      It is from the Invoice Line and I have grouped as customer will often have one of each colour on the same invoice therefore same date

      I managed this more by luck than skill so any help much appreciated

       

       

      The x axis is

      ExecuteSQL ("SELECT

      "Date"

      FROM "customer_Invoice_LineItem"

      WHERE "_Mach Id No" =? AND "StkDesc" LIKE '%TONER' AND qty >0

      GROUP BY "date" ";

      "|" ; "" ;

      Machine::_ID)

       

      The Y axis is

      ExecuteSQL ("SELECT

      count (*)

      FROM "customer_Invoice_LineItem"

      WHERE "_Mach Id No" =? AND "StkDesc" LIKE '%TONER'

      GROUP BY "date"

      ";

      "|" ; "" ;

      Machine::_ID)

       

      Works a treat but it shows the date as 2012-06-29 and I would like to show the UK date format 29/06/2012 or even better just group by Month and Year eg June 12

       

      Can anyody please tell me how I need to do this

       

      Many thanks

       

      Cathy

        • 1. Re: Reformatting the results of a SQL date
          comment

          Gingernut wrote:

           

          it shows the date as 2012-06-29 and I would like to show the UK date format 29/06/2012 or even better just group by Month and Year eg June 12

           

          You can convert a text "date" to an actual date using the Date() function, e.g. =

           

          Let ( [

          dateValues = Substitute ( Textdate ; "-" ; ¶ )

          ] ;

          Date (

          GetValue ( dateValues ; 2 ) ;

          GetValue ( dateValues ; 3 ) ;

          GetValue ( dateValues ; 1 )

          )

          )

           

          This will then be displayed in the date format being used by your file.

           

           

          However, it sounds like you have a bunch of these text "dates" in the same field (or variable), so you will need to loop over them and convert them one-by-one. Perhaps it would be better to move to a "native" (i.e. not SQL) method of driving your chart?

          1 of 1 people found this helpful
          • 2. Re: Reformatting the results of a SQL date
            Gingernut

            Thanks you I really wanted tom use the SQL option and as luck would have it found this solution below - May help others

             

            This worked for me

             

            http://seedcodenext.wordpress.com/2012/04/21/executesql-date-formats/

             

            ExecuteSQL – Date Formats

            April 21, 2012  

            The problem.

            SQL, including the new ExecuteSQL calc function in FileMaker 12, returns dates in this format: YYYY-MM-DD. This can be a bit of a pain if you have a string of text like this and just want to transform the date part:

            1170 - 2012-03-09 - - Keep: Beta Presentation @ BRN Co
            1171 - 2012-03-15 - 08:30:00 - Call Arthur Murray to finalize data
            1172 - 2012-03-16 - 12:30:00 - Conference call with Melinda
            1173 - 2012-03-20 - 11:15:00 - @ Breakfast

            The calc for the results above looks like this: ExecuteSQL ( “SELECT id, DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )

            The solution.

            Jason Young discovered that you can cast the dates as text in your results simply by concatenating them with a bit of text, so instead of using just the field DateStart in your query, you can use ” || DateStart to get this:

            1170 - 3/9/2012 - - Keep: Beta Presentation @ BRN Co
            1171 - 3/15/2012 - 08:30:00 - Call Arthur Murray to finalize data
            1172 - 3/16/2012 - 12:30:00 - Conference call with Melinda
            1173 - 3/20/2012 - 11:15:00 - @ Breakfast

            The calc for those results looks like this: ExecuteSQL ( “SELECT id, ” || DateStart, TimeStart, Summary from SampleEvents” ; ” – ” ; “¶” )

            Yes, FileMaker supports concatenate in it’s select statements.

             

            NB The '' is two single quotes as opposed to double ' ' but without the space

            • 3. Re: Reformatting the results of a SQL date
              beverly

              good answer from Michael (as usual!) if you are returning one value.

               

              The ExecuteSQL() allows these datetime functions:

                   MONTH(), DAY(), YEAR()

              and they behave the same way that the FMP functions do. You could "pull-apart" and then concatenate (|| or +):

               

                   SELECT DAY(\"Date\") + '/' + MONTH(\"Date\") + '/' + YEAR(\"Date\") AS au_date

               

              for June 12 (I'm assuming you see that as year):

               

                   SELECT MONTHNAME(\"Date\") + ' ' + RIGHT(YEAR(\"Date\"),2) AS mo_yr

               

              STRVAL(\"Date\") may be another option, but I don't know how it behaves in different OS.

               

              Thanks,

              Beverly

              • 4. Re: Reformatting the results of a SQL date
                beverly

                Does this provide the dd/mm/yyyy format that you desire?

                Thanks,

                Beverly

                • 5. Re: Reformatting the results of a SQL date
                  Gingernut

                  Using the MONTH YEAR example above how do I then group the Month Year data?

                   

                  You have been very helpful I am just struggling even with my SQL reference from you

                   

                  Thank you

                  • 6. Re: Reformatting the results of a SQL date
                    Gingernut

                    Yes

                     

                    The ‘’|| did the job – still working in the Month Year

                     

                    Cathy

                    • 7. Re: Reformatting the results of a SQL date
                      beverly

                      SELECT MONTHNAME(\"Date\") + ' ' + RIGHT(YEAR(\"Date\"),2) AS mo_yr

                       

                      Instead of GROUP BY did you mean ORDER BY (sorting)?

                       

                           ORDER BY \"Date\"

                      should give you the correct chronological order. Use DESC at the end to reverse the sort.

                       

                      GROUP BY is used when you have any fields with the aggregates (SUM, COUNT, etc.) You must list all other fields in the GROUP BY clause to make the statement valid. You don't use any aggregates, so this is un necessary.

                       

                      Or are you trying to get a DISTINCT listing of month/year (filtered down to one of each found - no duplicates)?

                       

                           SELECT DISTINCT MONTHNAME(\"Date\") + ' ' + RIGHT(YEAR(\"Date\"),2) AS mo_yr

                       

                       

                      As for putting these in the charts... I always create a mo_yr auto-enter text field which is

                           Year(date) & Right("00" & Month(date), 2)

                       

                      This will always sort correctly as "text". This is used in the Charts to "group" and I can pull it apart to make the chart show

                           'JUN 2003' instead of '200306'

                      This field also allows me to subsummarize in report (grouping!)

                       

                      HTH,

                      Beverly

                      • 8. Re: Reformatting the results of a SQL date
                        Gingernut

                        Thanks so much again – Doh! I completely  missed the Group By idea of using totals. In one case I do only want to show the total number of toners per month so I need the SUM in my SELECT statement.

                         

                        Brain is frazzled at the moment so am going to take a well deserved break

                         

                        Thanks again