Skip to main content
Inspiring
Jun 17, 2008 6:55 PM UTC
Question

SQL Between dates

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.
This topic is closed to new replies. Start a new post to keep the conversation going.

8 replies

Inspiring
Jun 17, 2008 8:57 PM UTC
-==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.

Translate
Inspiring
Jun 17, 2008 7:36 PM UTC
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.

Translate
wkolczAuthor
Inspiring
Jun 17, 2008 7:24 PM UTC
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.
Translate
Participating Frequently
Jun 17, 2008 8:02 PM UTC
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
Translate
Inspiring
Jun 17, 2008 8:50 PM UTC
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.


Translate
Participating Frequently
Jun 17, 2008 7:14 PM UTC
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;
Translate
Inspiring
Jun 17, 2008 7:12 PM UTC
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.
Translate