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.

[Newsgroup_User]

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.

[paross1]

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;

[wkolcz]

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.

[paross1]

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

[-__cfSearching__-]

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.

[Dan_Bracuk]

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.

[Newsgroup_User]

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.

[-__cfSearching__-]

-==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.