8 Replies Latest reply on Jun 17, 2008 2:52 PM by Dan Bracuk

    SQL Between dates

    wkolcz
      I am trying to pull information between 2 argument dates.

      Select offHours, tsCode, toDate
      FROM timeSheetDays
      WHERE uniqname = '#arguments.uniqname#'
      AND TODate BETWEEN #arguments.startDate# and #arguments.endDate#

      It comes out as:
      Select offHours, tsCode, toDate FROM timeSheetDays WHERE uniqname = 'wkolcz' AND TODate BETWEEN 2008-06-1 and 2008-06-31

      But I am not getting any results. What am I missing? I am using SQL server.
        • 1. Re: SQL Between dates
          Level 7
          What type of field is 'TODate'? Is it a date field, a string field, a
          number field? You may be having a problem with data matching. I do not
          know if SQL server will automatically convert a date string (like you
          are providing) to a date value.

          You would probably be better served using <cfqueryparam...> to make sure
          the data types match.

          I.E. TODate BETWEEN <cfqueryParam value="#arguments.startDate#"
          cfsqltype="cf_sql_date"> AND <cfqueryParam value="#arguments.endDate#"
          cfsqltype="cf_sql_date">

          Of course you would want to make sure the sql type parameter matches the
          actual type of the database column.
          • 2. Re: SQL Between dates
            paross1 Level 2
            What is the datatype of the TODate column? Are #arguments.startDate# and #arguments.endDate# date/time objects (doubtful)? Assuming that TODate is a date/time, you may need to CAST or CONVERT the target variables to date/time objects so that you aren't comparing apples to oranges.

            Phi;
            • 3. Re: SQL Between dates
              wkolcz Level 1
              That is what I thought. The toDate is a date/time. How can I cast the arguments as date. I tried CAST(#arguments.startDate# AS Date) but it exploded.

              Oddly TODATE >= #arguments.startDate# works, but <= #arguments.endDate doesn't.
              • 4. Re: SQL Between dates
                Level 7
                projectproofing wrote:
                > That is what I thought. The toDate is a date/time. How can I cast the arguments
                > as date. I tried CAST(#arguments.startDate# AS Date) but it exploded.
                >
                > Oddly TODATE >= #arguments.startDate# works, but <= #arguments.endDate doesn't.
                >

                IN CFML it would be createODBCDate() or better yet the <cfqueryparam
                ...> tags I mentioned in my previous reply.

                • 5. Re: SQL Between dates
                  paross1 Level 2
                  DATE is not a valid datatype for MS SQL (how about datetime or smalldatetime). How about setting the value of #arguments.startDate# and #arguments.endDate# to something that matches one of the style arguments that the MS SQL CONVERT() function recognizes. Or, as Ian has suggested, try using cfqueryparam with the appropriate cfsqltype for a MS SQL datetime object, such as CF_SQL_TIMESTAMP.

                  Phil
                  • 6. Re: SQL Between dates
                    -==cfSearching==- Level 4
                    projectproofing wrote:
                    > TODate BETWEEN 2008-06-1 and 2008-06-31
                    > ..
                    > Oddly TODATE >= #arguments.startDate# works, but <= #arguments.endDate doesn't.

                    BTW, assuming it is not a typo ... the value "2008-06-31" is not a valid date anyway. Verifying the variables contain valid date strings, and properly converting them to datetime objects (as explained in the previous responses) should resolve your problem.


                    • 7. Re: SQL Between dates
                      -==cfSearching==- Level 4
                      -==cfSearching==- wrote:
                      > ... should resolve your problem.

                      Of course I am making the assumption that BETWEEN is the correct operator to be using here. If the "ToDate" values included a non-zero time (ie 2008-06-17 21:53:18) then BETWEEN may not be the correct operator to use in your query.

                      • 8. Re: SQL Between dates
                        Dan Bracuk Level 5
                        quote:

                        Originally posted by: projectproofing
                        That is what I thought. The toDate is a date/time. How can I cast the arguments as date. I tried CAST(#arguments.startDate# AS Date) but it exploded.

                        Oddly TODATE >= #arguments.startDate# works, but <= #arguments.endDate doesn't.

                        If it were me, I'd use createdate and the necessary string functions to create date variables in the calling template and pass these date variables to the function.

                        On a related notes, for timestamp fields, it is often better to do this:
                        where somefield >= date1 and somefield < date2

                        than it is to use between because of the time portion of the field.