CF and MySQL No Speaking the Same Date !

0000-00-00 is NOT a valid date, even in MySQL if you run it in strict mode.
because you are running MySQL in relaxed mode, it treats the 0000-00-00
date values internally as NULL. Java does not do that.
you need to add a connection dtring to your datasource in cf admin to
tell it how to treat these dates. more info in MySQL reference manual.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Thanks for the reply, however, this is not addressing the problem. We are talking about symantics here. I may call it formatting, however, I understand that it is not what Adam and Dan are saying.

Dan, I know where the error is coming from. It is coming from the date field in CF which is looking for a date in MySQL 5. I am calling the representation "format" because thats what it means. The representation of the date that Coldfusion expects is not happening from MySQL. Thus the error:

Value '0000-00-00' can not be represented as java.sql.Date

That is coming from ColdFusion 8.

As Adam suggested, I included the code. I also included the error message itself. This error only comes when I include the date field in the SQL statement Coldfusion is not accepting the value because it is not a "Value '0000-00-00' represented as java.sql.Date."

I have done a lot of looking and much of it is just not making sense to me. Which is why I am here asking for help, not a further investigation of what is wrong. I have looked at the options that were given and still they are not making since, Even the link that Adam provided leads to a page that cannot be displayed.

My question simply is - does anyone know how I can get Coldfusion and Mysql5 on the same page as far as the date issue is concerned?

Thanks,
Ell

First, everytime you think of the word format, re-read Adam's answer.

Second, read your error message. Then figure out where that value came from.

Hello,

Thanks for the response!

Still trying to work out the date communication between Coldfusion 8 and MySQL 5. MySQL seems to use a format that is not the expected format that Coldfusion needs to display, update or store the date. Coldfusion seems to be expecting a "java.sql.date which is not as MySQL uses. Below is the error that I am getting. I have also given the code that is being used below that. I have had Coldfusion for a while but and just now starting to try to use it. I would greatly appreciate any assistance.

Thanks,
Ell


Error:

Error Executing Database Query.
Value '0000-00-00' can not be represented as java.sql.Date

The error occurred in C:\ColdFusion8\wwwroot\CALC\admins\adminsPage.cfm: line 3

1 : <cfparam name="grades" default="">
2 : <cfparam name="PageNum_Recordset1" default="1">
3 : <cfquery name="Recordset1" datasource="students" username="me1" password="152577">
4 : SELECT grades.stuId, student.lName, grades.date, grades.subNum, subjects.subName, grades.score
5 : FROM student INNER JOIN grades INNER JOIN subjects


Code Used:

<cfparam name="grades" default="">
<cfparam name="PageNum_Recordset1" default="1">
<cfquery name="Recordset1">
SELECT grades.stuId, student.lName, grades.date, grades.subNum, subjects.subName, grades.score
FROM student INNER JOIN grades INNER JOIN subjects
ON student.stuID = grades.stuID and subjects.subNum = grades.subNum
WHERE tlName = 'BOSTON'
ORDER BY grade, lName, fName, subName, date

> I am using the CF wizard in Dreamweaver to display a recordset that has a date
> column in MySQL. MySQL needs that date as YYYY-MM-DD. This is a huge problem
> because I need to see MM-DD-YYYY. CF is throwing an error on this column. I
> suspect that it is looking for a date field formated differently that
> YYYY-MM-DD. Anyone know how I can get CF and MySQL on the same page and
> display and/or edit the date as I need it to be through Dreamweaver?

OK, first thing first, you need to understand that MySQL and CF don't deal
with "dates formatted as mm/dd/yyyy" or "dates formatted as yyyy-mm-dd".
That's a human thing. Computers just deal with "dates" (or datetimes, or
timestamps, or shortdates, or some variation on that theme).

In CF a date is a specific sort of object (like a string or a struct or
what-have-you). To create a date, CF provides a number of functions (look
them up in the docs, here
http://livedocs.adobe.com/coldfusion/8/htmldocs/functions-pt0_05.html),
such as createDate(), parsedateTime(), etc, depending on the source data
being provided.

Often the date information is entered into the system as a string (like a
user filling in a form), so the first thing your code should be doing in
this instance is taking that string-data and converting it to a date. Only
then do you start doing anything else with it, like using it in logic, or
storing it.

DB's also use a specific data type to store dates, and this will be
different from CF's way of storing a date, so some sort of transformation
needs to be done between the two. Fortunately the people who write things
like CF and DB drivers know this is a common thing one needs to do, so they
pretty much do all the work for you. All you need to do is to use a
<cfqueryparam> tag in your <cfquery>, which is CF's way of making sure the
DB receives the data its expecting.

The other way around - outputting the date object - one can convert the
date object back into a string using functions like dateFormat() or
lsDateFormat(), which allow the coder to specify an output format (days and
moths and years in specific orders, with specific separators, etc). One
can just output a date object directly, but then CF makes a call to output
the date in a generalised format, which is not very aesthetically pleasing
- something like {ts 'yyyy-mm-dd HH:mm:ss.lll')}. That format is favouring
function over form, somewhat. But it gets the point across.

One other thing.

If you're having problems with your code... it's much easier to work out
the cause of your problem (rather than just editorialising about it), if
you *post the code in question*. Fortunately in this case,
"editorialising" is probably an important first step in sorting your issue
out.

--
Adam