[thelist] Tip: MySQL - Searching against dates/datetimes

[Frank]

<tip type="MySQL - Searching against dates/datetimes" author="Frank Marion">

Even though conceptually related, Date and Date/Time are not the same data 
type.  Imagine you have the following scenario: a column in your database 
is set as date/time, but you have only a date to search against. A query 
using a Date value against the column will return an error. A query using a 
your value converted to date/time will fail (because if you are off even by 
a second, there's no match). So what do you do?

What DO you do?

Use MySQL's built in function DAYOFYEAR(my_date). January 1st would of 
course be [1] and January 24th would be [24] and so on. Most programming 
languages should already have such a function. Coldfusion for example has 
DayOfYear(). When you convert your date to the DayOfYear, you can then 
proceed to qualifiers such as

WHERE DAYOFYEAR(date_order_placed) = DAYOFYEAR(current_date())

<cfset search_date = DayOfYear(search_date)>
WHERE DAYOFYEAR(date_order_placed) = #search_date#

Bonus tidbit: if you want to find a day in this week

WHERE WEEK(date_order_placed,0) = WEEK(current_date(),0)

It's worth getting to know your databases built-in functions, as well as 
your favourite programming language's.

</tip>


--
Frank Marion     lists at frankmarion.com      Keep the signal high.