SQL Query returns no value

[bataraguru]

Hi,
I'm having a problem with my query.

<cfquery name="display_date" datasource="#dsn#">
SELECT * FROM reservation
WHERE (checkin >= 03/05/2005 OR checkout >= 03/05/2005) AND
RoomNo = 'SM001'
</cfquery>

Table reservation:
checkin (date/time) checkout (date/time) RoomNo (char)
1.03/05/2005 06/05/2005 SM001
2.01/05/2005 02/05/2005 SM001
3.13/05/2005 16/05/2005 SM001

Base on my table and query the result should be return 2 value (1 & 2). But in my problem there is no result.
if i change the '>=' to '=' it will return a value (1).
Could anybody help me on this.

Thanks in advanced.

 

[TruthInSatire]

(checkin >= 03/05/2005 OR checkout >= 03/05/2005)

number 2 doesn't satisfy these conditions. I hope you ment 1 & 3.

try putting pound signs around your dates.

(checkin >= [b]##[/b]03/05/2005[b]##[/b] OR checkout >= [b]##[/b]03/05/2005[b]##[/b])

Beware of programmers who carry screwdrivers.

 

[bataraguru]

oh yaa.. it should have 1 & 3.
btw, using '##' did not solve my problem.
and i have try using cfqueryparam just like below and still doesnt produce the output that i want.
<cfquery name="chalet" datasource="OrangeCountryResort">
SELECT * FROM Reservation
WHERE room = '#RoomNo#' AND (checkin > <cfqueryparam value="#today#" cfsqltype="CF_SQL_DATE"> OR checkout > <cfqueryparam value="#today#" cfsqltype="CF_SQL_DATE">)
</cfquery>

 

[r937]

your problem will disappear (and your query will run faster, too) if you follow "best practice" and let the database use its own internal current date/time value, rather than you feeding it a string from an external source (coldfusion) which it must first parse and then validate to ensure that it's a valid date string, and then convert to an internal representation of a date/time

<cfquery name="display_date" datasource="#dsn#">
select *
from reservation
where RoomNo = 'SM001'
and ( checkin >= Now()
or checkout >= Now() )
</cfquery>

note carefully: in the above query Now() is the microsoft access function, not the coldfusion function

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ksea]

you using SQL server?


<cfquery name="display_date" datasource="#dsn#">
select *
from reservation
where RoomNo = 'SM001'
and (checkin >= #createodbcdate(now())# or checkout is #createodbcdate(now())# )
</cfquery>

 

[bataraguru]

thanks r937 & ksea for your suggestion..
sorry to say that the output still the same..

 

[jalpino]

#3 has invalid dates, so shouldn't the results only return #1?

Table reservation:
checkin (date/time) checkout (date/time) RoomNo (char)
...
3.13/05/2005 16/05/2005 SM001

 

[bataraguru]

it should return 2 dates (1 & 3)

 

[jalpino]

hi bataraguru,

#3 contains invalid dates, if you are using the format 'mm/dd/yyyy'. There is no 13th or 16th month. What format are your dates in, and what database are you using? I am using SQL server and I can't insert the text '13/05/2005' into a date field. Is your 'date' column of type char?

jalpino

 

[bataraguru]

The format in the database = dd/mm/yyyy.
using microsoft access dbase. checkin & checkout are defined as Date/Time.

Thanks

 

[GUJUm0deL]

Since your date/time is setup as dd/mm/yyyy in MSAccess, why not use CF to format the date in the way you want?

<cfquery name="display_date" datasource="#dsn#">
select * 
from reservation
where RoomNo = 'SM001'
      and (checkin >= "dateformat(now(),'dd/mm/yyyy')" or checkout >= "dateformat(now(),'dd/mm/yyyy')")
</cfquery>

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]

 

[bataraguru]

thanks for the reply GUJUm0deL, still dont solved my problems. I wonder why? Its there any restriction or format i have to declare in the GENERAL data type settings like Input Mask, Caption ..

thanks..

 

[GUJUm0deL]

How did you decalre the date to be as dd/mm/yyyy in the first place? I looked at my MS Access and I only see the following the format types: General Date, Long Date, Medium Date, Short Date. The only thing close to your date format is the medium date and thats as dd-mo-yy.

Are you sure there is actual data in the dB for the query to pull any results from? Post some code or more info.

[sub]
____________________________________
Just Imagine.

http://www.imajinarts.com

[sub]