Retreiving date information from tables

Hi all I'm using SQL and ColdFusion to make a query the retreives a set of dates from my database. I have a table called ORDERS that has a fieldname of OrderDate and I want to find all orders in the year 2004, there is only one order in 2003.

I have tried using SELECT OrderDate FROM ORDERS WHERE OrderDate BETWEEN 1-1-2004 AND 31-12/2004 but this didn't work for me. I have recently tried
SELECT OrderDate
FROM ORDERS
WHERE (ORDERS.OrderDate > #"31-12-2003"#) but again this still outputs the order from year 2003 and have looked on many tutorials but can't find the right combination.


Could anyone help me at all?

Is orderDate a TRUE date/time field, or is it a string field that happens to be holding a date? You'll have to look at the data type of the column to confirm this.

If it IS an actual date/time field, you can try using createODBCDate() to format the date correctly.

I've just had a look at the table and OrderDate has a data type of date/time, does that clarify this issue for you?

I would try something like:

SELECT OrderDate
FROM ORDERS
WHERE (ORDERS.OrderDate > #createODBCDate( "12/31/2003" )#)

Also confirm that the less than sign means dates AFTER the specified date. You may need to use < instead, I just don't remember.

thanks for that yes it did exactly what I wanted it to do, incldientally although you are probably verk knowledgeable in the area of coldfusion how did you know about tis command createODBCDate and in what situations to use it?
Ta

The first place you should look when you want to do something is the CFML reference, for tags and functions. If you can't find anything, try the developers exchange for a custom tag or CFC, and then look at www.cflib.org for a user-defined function. If nothing is there either, then you'll have to build something yourself.

Over time you get to know the available functions, just as it is when learning any language. createODBCDate() is used when you need a date in the ODBC date format, usually for interacting with databases.

Thanks for the advise, the page contains very small text though, quite hard to read if I'm honest

Yes, the text is small. Just overlook that and search through the library to find UDF's that you might find useful. Aside from the small text cflib is very useful.

Incidentally if I decided to use the BETWEEN statement e.g. WHERE OrderDate BETWEEN 1/1/2004 AND 31/12/2004 then could i also use createODBCDate() or a similar command that lets you search between date values etc etc?

Yes. The CF functions are processed BEFORE the statement gets forwareded to the RDBMS. So by the time the database gets the statement, the functions have already been substituted for time stamps.