Solved

CFQUERY TO PERFORM TIME COMPARISONS

Posted on 2004-04-23
11
271 Views
Last Modified: 2013-12-24
Hi,

I need help with the following code...

<cfset stime = dateadd("n",-10, time)>
<cfset etime = dateadd("n",10,time)>
<!--- Query of Query Running from Master Query--->
<cfquery name="notify" dbtype="query">
select username, name, time
from master
where username = '#master.username#' and (time > #stime# or time < #stime#)
</cfquery>

Above, I'm attempting to perform a time comparison where stime and etime is the window where time should be called on. However this doesn't seem to work.

Thanks for your help !
0
Comment
Question by:aescribens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 10906329
Hi aescribens,

two things.

1. you have #stime# for both bits, second one should be etime ie < #etime# and you will probably need to use the CreateODBC commands, either CreateODBCDateTime or CreateODBCTime depending on what's actaully contained in stime and etime.

where   username = '#master.username#'
and     (time > #CreateODBCDAteTime(stime)# or time < #CreateODBCDateTime(etime)#)

let me know.

Regards
Plucka
0
 
LVL 18

Expert Comment

by:Plucka
ID: 10906333
aescribens,

Also should be

and time < not or if you want between the two times, otherwise it will show everything.

and     (time > #CreateODBCDAteTime(stime)# AND time < #CreateODBCDateTime(etime)#)

Regards
Plucka
0
 
LVL 1

Author Comment

by:aescribens
ID: 10906345
sorry about that - I do have stime and etime.

I should also add that I have the DB field as "varchar" not "date/time".
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 18

Expert Comment

by:Plucka
ID: 10906358
Well you shouldn't other wise you cant do what you want to do without complicated string manipulation.

What it's in the field, paste an example of the value of time or Change your db field to DateTime.
0
 
LVL 1

Author Comment

by:aescribens
ID: 10906386
I would change it but I have many other functions that are already using that field and it would be a headache.

This is what I have...

time = 02:00 (varchar)
stime = <odbctime -10 minutes from #time#>
etime = <odbctime 10 minutes from #time#>

I'm not using any dates for this, only time.  Thanks.
0
 
LVL 18

Expert Comment

by:Plucka
ID: 10906403
Ok,

The dateAdd is creating a date stamp.

Assuming your using a 24 hour clock a string comparison might work.

So first you need to get your times back to a string.

<cfset stime = TimeFormat(dateadd("n",-10, time), "HH:MM")>
<cfset etime = TimeFormat(dateadd("n",10, time), "HH:MM")>

Then change your query to.

(time => '#stime#' AND time <= '#etime#')

Make sure you use AND and make sure you use the single ' quotes.
0
 
LVL 1

Author Comment

by:aescribens
ID: 10907217
What I did...

<cfset qstime = timeformat(stime,"HH:MM")>
<cfset qetime = timeformat(etime,"HH:MM")>
<cfquery name="notify" dbtype="query">
select username, name, time
from master
where username = '#master.username#' and time => '#qstime#' and time <= '#qetime#'
</cfquery>

What I get...

Query Of Queries syntax error.
Encountered "time = >" at line 0, column 0. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,  
 
The error occurred in C:\Websites\notifycom\notify.cfm: line 27
 
25 : select username, name, time
26 : from master
27 : where username = '#master.username#' and time => '#qstime#' and time <= '#qetime#'
28 : </cfquery>
0
 
LVL 17

Accepted Solution

by:
anandkp earned 300 total points
ID: 10907546
try this

<CFQUERY NAME="notify" DBTYPE="query">
      select username, name, time
      from master
      where username =       <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#master.username#">
      and (time >      <CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#stime#">
      or time < <CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#stime#">)
</CFQUERY>

hth

K'Rgds
Anand

On a side note : avoid using probable keywords such as "date" , "time" as fieldnames in ur tables ..
0
 
LVL 1

Author Comment

by:aescribens
ID: 10907661
Anand,

Do you recommend keeping the field datatype as "varchar" or should I make an effort and change it to date/time.  Keep in mind that I'll NEVER use the "date"; I'm only working with time on this part.

Thanks,

ACE
0
 
LVL 1

Author Comment

by:aescribens
ID: 10907775
Another thing...  An example of the value that is currently in the "time (varchar)" field is "23:00" or "15:00" or "11:00", ect...

Thanks,
ACE
0
 
LVL 1

Author Comment

by:aescribens
ID: 10908259
Anand,

This worked !!!!

where username = <CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="#master.username#">
     and (<CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#time#"> > <CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#stime#">
     or <CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#time#"> < <CFQUERYPARAM CFSQLTYPE="CF_SQL_TIMESTAMP" VALUE="#stime#">)

Thanks for your guidance !!!

ACE
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question