Skip to main content
W
wkolcz
Inspiring
June 17, 2008
Question

SQL Between dates

  • June 17, 2008
  • 5 replies
  • 801 views
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 has been closed for replies.

5 replies

-
-__cfSearching__-
Inspiring
June 17, 2008
-==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.

N
Newsgroup_User
Inspiring
June 17, 2008
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.

W
wkolczAuthor
Inspiring
June 17, 2008
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.
P
paross1
Participating Frequently
June 17, 2008
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__-
Inspiring
June 17, 2008
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.


P
paross1
Participating Frequently
June 17, 2008
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;
N
Newsgroup_User
Inspiring
June 17, 2008
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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices