asked on

CFQUERY TO PERFORM TIME COMPARISONS

Hi,

I need help with the following code...

<cfset stime = dateadd("n",-10, time)>
<cfset etime = dateadd("n",10,time)>

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

Plucka

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

Plucka

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

aescribens

ASKER

sorry about that - I do have stime and etime.

I should also add that I have the DB field as "varchar" not "date/time".

Plucka

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.

aescribens

ASKER

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.

Plucka

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.

aescribens

ASKER

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>

ASKER CERTIFIED SOLUTION

anandkp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aescribens

ASKER

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

aescribens

ASKER

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

aescribens

ASKER

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