New to CF, can't figure out date comparisons...

[dakota81]

I'm passing two date fields to a CF page, and trying to pull records from a table that fall between that date range. Can't get it right...

So if the table's field is "date" and the two values passed in are valid dates, FORM.startdate and FORM.enddate, what is the SQL syntax I need to use?

 

[Extras]

Should it not be...

SELECT YourFields
FROM YourTable
WHERE (YourTable >= FORM.startdate) AND (YourTable <= FORM.enddate)


HTH

 

[Extras]

Am sorry i forgot the #s hope this is works for you...


SELECT YourFields
FROM YourTable
WHERE (YourTable.Date >= #FORM.startdate#) AND (YourTable <= #FORM.enddate#)

 

[dakota81]

Thanks; seems my other trouble now is figuring out how to compare a varchar field to pick out if the field is empty. Tried a zero-length string, doesn't work; tried NULL, doesn't work... I guess I just got so accustomed to M$ Access, where you don't have to be a brain surgeon to get the job done...

 

[Extras]

Sorry cannot help you on that front.

Might want to start a separate thread on that - with an appropriate title so someone familiar with SQL server can help you on that front.

If you are trying to see if a varchar field is empty - can you not check and see if the length of the field is zero?

#Trim(Len(form.field)) EQ 0)

 

[dakota81]

Through trial and error I finally figured out the correct syntax. On the date comparisons, you need single quotes around the variable. Then to compare an empty field, it's like: '...AND FieldName Is Null'

 

[hyderabad2976]

you could even do this
SELECT YourFields
FROM YourTable
WHERE YourTable.Date BETWEEN #FORM.startdate# AND #FORM.enddate#