Solved

CFQUERY TO PERFORM TIME COMPARISONS

Posted on 2004-04-23
11
242 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
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
 
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
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: 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OpenLDAP set password to expire 7 524
Transferring website to another host 7 57
Coldfusion Datefield problem 2 96
Website being blocked? 3 122
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…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

948 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

21 Experts available now in Live!

Get 1:1 Help Now