Solved

CFQUERY TO PERFORM TIME COMPARISONS

Posted on 2004-04-23
11
231 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
  • 6
  • 4
11 Comments
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now