i think your problem is that you store date values as text
instead of
date objects and the 'date' you pass to DAYOFWEEK function is
not in
correct format. mysql expects a date in YYYY-MM-DD or
YYYY/MM/DD format
while you are passing a date string as YYYY/DD/MM. thus some
of the
'dates' you pass to the function will validate as dates but
others will
not - in any case even the ones that do validate will be NOT
the dates
you espect (i.e. if you pass '2008/01/10' expecting it to
return day of
week for October 1, 2008, it will actually return day of week
for
January 10, 2008 because of the wrong date format you are
passing to the
function)
run this simple test:
<cfquery name="q1" datasource="you_dsn_here">
SELECT DAYOFWEEK(RIGHT('11:00 2008/14/09', 10)) AS date1,
DAYOFWEEK(RIGHT('11:00 2008/09/14', 10)) AS date2
FROM some_table_name_here
</cfquery>
<cfoutput>DATE1: #q1.date1# | DATE2:
#q1.date2#</cfoutput>
you will see that the above returns NULL (empty string) for
DATE1
(because the date passed to function - day 9 of month 14 of
year 2008 -
in is not valid), and 1 for DATE2.
lesson learned: a) store your dates as dates to avoid this;
b) read you
db docs to see what a function expects as arguments.
hth
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
15
Replies
AdChoices