ColdFusion CreateODBCDate Love

I was writing a mySQL query that only included dates in the results after today, and thought I’d share a quick problem I ran into.

SELECT column1, column2
FROM table
WHERE columnDate >= NOW()

Unfortunately, the NOW() function in mySQL will not include today.  Of course if I wanted to exclude today I’d take off the =.  I’m sure there’s a way to fidget with it and get it to do what I want in mySQL, but with a simple ColdFusion function I was able to get the result I was looking for.

SELECT column1, column2
FROM table
WHERE columnDate >= <cfqueryparam value="#CreateODBCDate(now())#" cfsqltype="cf_sql_date" />

Sexy!

3 comments

  1. Timothy Leach says:

    Or.. you could replace NOW() with CURDATE().

    The reason now won’t work is because it includes the time as well as date, so it is literally now, this second, so if it’s early and that’s a dateTime column is will only return dates before “now”, excluding those later in the day. CurDate() just returns the date, which judging from your SQL is what you really want.

  2. Timothy Leach says:

    Try CURDATE(), it returns just today’s date instead of the current date and time like Now().

Leave a Reply

Your email address will not be published. Required fields are marked *