[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

CFQUERY TO PERFORM TIME COMPARISONS

Posted on 2004-04-23
11
Medium Priority
?
281 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
Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

 
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 1200 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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