0

I am adding dates into my MySQL database using a form. The column has a data type of datetime, with a column name of _date.

To output my data I am using this code:

<cfoutput>
    <cfquery name="vehiclelogDate" datasource="#datasource#">
        select ID, '_date'
        from   vehicle_log
        where  vehicle_id = <cfqueryparam value="#url.id#">
        order by _date ASC
    </cfquery>

    <cfset fist_year = DATEPART("yyyy",'#vehiclelogDate._date#')>

    #first_year#
</cfoutput>

When I do that I recieve the error: The value _date cannot be converted to a date.

I recently converted from an Access test database to MYSQL and now this error has appeared.

Can someone help?

Leigh
  • 28,765
  • 10
  • 55
  • 103
Brian Fleishman
  • 1,237
  • 3
  • 21
  • 43
  • 1
    Dump out the results of the query using `cfdump` and post a screen cap of the output to your question. It would help to see the data that is coming back from the database. – Scott Stroz Feb 02 '15 at 14:26
  • 1
    Strangely enough, the output just shows just the variable name, _date. If I change my query to specify 'select *', then the page loads without error.? I don't understand? Those column names definitely exist. – Brian Fleishman Feb 02 '15 at 14:57
  • Does the same query works at DB end? – Anit Kumar Feb 02 '15 at 15:00
  • 1
    Side note, though it won't cause an error, there's no need to put the cfquery *inside* the cfoutput tags. CFOutput is only needed when displaying/outputting variables. Also, to [avoid implicit conversion issues](http://stackoverflow.com/questions/27049918/coldfusion-parameterizing-a-querie/27066113#27066113), do not forget to specify the `cf_sql_type` with all parameters. – Leigh Feb 02 '15 at 15:38
  • See Adam's answer. I assumed those were 'back ticks' around `_date`. – Scott Stroz Feb 02 '15 at 16:38

1 Answers1

6

This:

select ID, '_date'

It's not returning the ID and _date columns, it's returning the ID column, and the string _date.

You want something like this:

select ID, _date
Adam Cameron
  • 29,677
  • 4
  • 37
  • 78
  • 2
    @Brian - I am guessing you are trying to escape a special column name. It is worth noting the common escape character in MySQL is a [back tick](https://en.wikipedia.org/wiki/Backtick), which is different than a single quote. Assuming it is really required, you might consider renaming the column to something else. That would avoid the hassle of escaping altogether. – Leigh Feb 02 '15 at 15:11
  • I thought the same. But I can't see how `_date` would need back-ticking. – Adam Cameron Feb 02 '15 at 17:44
  • @Leigh - What do you mean by a 'back tick'? – Brian Fleishman Feb 02 '15 at 18:20
  • @Adam - Yes, I do not think it is really required either. I tested it out and both "date" and "_date" worked fine for me (though neither is a great column name IMO). – Leigh Feb 02 '15 at 18:24
  • @BrianFleishman - We weren't sure why you were using single quotes around the column name. One possible reason is that you thought the `_date` column name was some sort of reserved word and needed to be escaped to a avoid a syntax error. In MySQL the escape character is a back tick. Since a lot of people confuse that with a single quote (like in your example) I thought that might be the reason for them - but it sounds like including the quotes around the column name was just a mistake ;) – Leigh Feb 02 '15 at 18:28