12 Replies Latest reply on Feb 18, 2013 7:42 AM by novarem

    sql DATE problems

    Gingernut

      Filemaker 12 Windows 7

       

      ExecuteSQL ("SELECT mcStockCode , Qty , ExtValue , mcDateSupplied

      FROM ConsumablesPartsFitted

      WHERE mcDateSupplied BETWEEN ? AND ?";"|";"";

      "01/01/2012";

      "31/01/2012")

       

      This query is only returning items with date 01/01/2012 to 05/01/2012 87 items instead of 598 items

       

      I have checked the date format is the same in the database ie / and not dots .

       

      If I change the date in the data to ALL show 01/05/2012 (ore before) they are picked up

      If I get all to be 06/01/2012 or later none show

       

      I have also tried adding DATE (mcDateSupplied) still does not work what am I missing please?

       

      I have also tried mcDateSupplied > 31/12/2011 AND mcDateSupplied < 01/02/2012

       

      Actually used Greater than OR Equal to but dont know how to type that

       

      Thanks in anticipation

       

      Cathy

        • 1. Re: sql DATE problems
          Nico_Kobes

          I'm not sure but isn't the format: mm/dd/yyyy ?

          • 2. Re: sql DATE problems
            jbante

            Have you tried this?

             

            ExecuteSQL ( ... ; Date ( 1 ; 1 ; 2012 ) ; Date ( 1 ; 31 ; 2012 ) )

            • 3. Re: sql DATE problems
              beverly

              In my tests, I found these to be valid with ExecuteSQL( ):

                 "YYYY-mm-dd"

              or

                 Date ( m ; d ; YYYY )

              when placed in the arguments.

               

              There may be other formats, but I don't believe that "dd/mm/yyyy" is ever acceptable in any SQL. Your 31 may have been read as month, when you intended day.

               

              If you have a field or variable with your date you may use:

                 Date ( Month ( $date ) ; Day ( $date ) ; Year ( $date ) )

              then this should work well.

               

               

              -- sent from my iPhone4 --

              Beverly Voth

              --

              • 4. Re: sql DATE problems
                Gingernut

                Am I supplosed to be applying the DATE (m;d;YYYY) to the field or the Argument please Have tried both and getting nowhere fast

                 

                I am now looking for single date I know to exist with the other WHERE fields matched If I take date out it works but cannot for life of me get it working. It is a caclulated field in Filemaker but with result as date would this make any difference?

                 

                Does it make a difference if I use 8 or 08 for the month

                 

                 

                // Name: Commission reports

                // Date: 11/20/2012

                ExecuteSQL ("SELECT invline.\"_InvNo\" , invline.\"BW Meter Used\" , invline.\"Col Copies_Prints Used Total\" , \"BW Copies Ext Cost\"+\"BW Prints Ext Cost\" , \"Col Copies Ext Cost\"+\"Col Prints Ext Cost\" , invline.\"Date\" , m.Name , m.\"Sales Rep\"

                FROM CustomerInvoiceLineItem invline

                LEFT JOIN Machine m ON invline.\"_Mach ID No\"=m.\"_ID\"

                WHERE \"Sales Rep\" LIKE ? AND NominalCode LIKE ? AND \"Date\" = DATE (8;31;2011)";"|";"";

                "CH";

                "1MR")

                 

                Many thanks

                 

                Cathy

                • 5. Re: sql DATE problems
                  taylorsharpe

                  You are supposed to apply the Date ( m ; d ; yyyy) to the argument and you did it correctly as Date ( 8 ; 31 ; 2011 ).  And, no, it doesn't matter if you use 8 or 08 for the month.  Leading zeros do not matter in the FileMaker date function.  You can even have calculation inside of it so you could have say Date ( 7 + 1 ; 31 ; 2011 ) would be the same as Date ( 8 ; 31 ; 2011 ) which is the same as Date ( 8 + 1 ; -1 ; 2011 ). 

                  • 6. Re: sql DATE problems
                    beverly

                    >> when placed in the arguments.

                     

                    WHERE \"Sales Rep\" LIKE ? AND NominalCode LIKE ? AND \"Date\" = ? ";"|";"";

                    "CH";

                    "1MR";

                    DATE (8;31;2011)

                    )

                     

                    • 7. Re: sql DATE problems
                      Gingernut

                      Bizarre

                       

                      // Name: Parts Supplied In date detail

                      // Date: 11/21/2012

                      ExecuteSQL ("SELECT mcDateSupplied , mcStockCode , Qty , mcStockDescription , \"Jobsheet No\"

                      FROM ConsumablesPartsFitted

                      WHERE mcDateSupplied = ?";"";"";

                      "09/01/2009")

                       

                      This returns the parts fitted on 1st September 2009 so is using US date format rather than SQL date format - thanks all for your comments but now I am more confused than ever if anyone has any ideas. 

                       

                      // Name: Parts Supplied In date detail

                      // Date: 11/21/2012

                      ExecuteSQL ("SELECT mcDateSupplied , mcStockCode , Qty , mcStockDescription , \"Jobsheet No\"

                      FROM ConsumablesPartsFitted

                      WHERE mcDateSupplied BETWEEN ? AND ?";"";"";

                      "11/01/2011";

                      "11/30/2011")

                       

                      This is returning all of November but is not what people have been suggesting is the format.

                       

                      Cathy

                      • 8. Re: sql DATE problems
                        beverly

                        OK, so "US date" seems to work. I just know that the Date() function works well and the ODBC date format works well. I tend to use both of these, as they are less confusing to the world.

                         

                        Beverly

                        • 9. Re: sql DATE problems
                          Gingernut

                          Thanks for your help Beverley have been making great use of your SQL handbook or whatever it is called the stuff that Filemaker does not tell you about in enough detail to make it useful. Really appreciate the time some of you guys put into making life easier for the rest of us.

                          • 10. Re: sql DATE problems
                            mrwatson-gbs

                            Hi,

                             

                            if you pass a date as text, e.g. "11/01/2011", it will be converted into a date using the current land-formats that are set in the file/system. I.e. if you are in britain that date will be interpreted as 11th of January.

                             

                            It is thus better to us an unequivocal method such as the reversed text "2011-11-01" or the Date function Date( month ; day ; year )

                             

                            Hope this helps.

                             

                            MrWatson from Germany

                            1 of 1 people found this helpful
                            • 11. Re: sql DATE problems
                              Gingernut

                              That's good to know thanks

                              • 12. Re: sql DATE problems
                                novarem

                                Curiously, FileMaker returns date with ISO format (yyyy-MM-dd) on the select clause but expects the local date format on the where clause (dd/MM/yyyy for french, MM/dd/yyyy for US etc.).