CodingForums.com - View Single Post - Storing ColdFusion date object into a relational database?

Gjslick · 04-20-2012, 07:29 PM

Ok, a few problems:

First, an insert statement uses the keyword "values", not "value" (even if there is only one value that you are inserting). Your SQL should look like this:

Code:

<cfquery name="qrytime" datasource="exampledsn" username="exampleuser" password="password" >
    INSERT INTO Time ( Date )
    VALUES ( '#myTableDate#' )
</cfquery>

So it's not necessarily an issue with your date format, although we don't know just yet because you need the correct SQL syntax first :)

Second, there is an issue with using words DATE and TIME for your table/column names. They are datatype keywords, and may or may not cause the parser to complain with where you're using them. In general, you shouldn't name columns or tables with these names.

And finally, you've read that the DATETIME datatype is stored using 8 bytes of space behind the scenes; not that it can only accept a format of 8 characters. It would be very hard to provide SQL Server of a full date/time value (such as in the format of "mm/dd/yyyy hh:mm:ss zzzz") in just 8 characters ;-)

04-20-2012, 07:29 PM	PM User \| #6
Gjslick Regular Coder Join Date: Feb 2009 Location: NJ, USA Posts: 476 Thanks: 2 Thanked 70 Times in 69 Posts	Ok, a few problems: First, an insert statement uses the keyword "values", not "value" (even if there is only one value that you are inserting). Your SQL should look like this: Code: <cfquery name="qrytime" datasource="exampledsn" username="exampleuser" password="password" > INSERT INTO Time ( Date ) VALUES ( '#myTableDate#' ) </cfquery> So it's not necessarily an issue with your date format, although we don't know just yet because you need the correct SQL syntax first :) Second, there is an issue with using words `DATE` and `TIME` for your table/column names. They are datatype keywords, and may or may not cause the parser to complain with where you're using them. In general, you shouldn't name columns or tables with these names. And finally, you've read that the DATETIME datatype is stored using 8 bytes of space behind the scenes; not that it can only accept a format of 8 characters. It would be very hard to provide SQL Server of a full date/time value (such as in the format of "mm/dd/yyyy hh:mm:ss zzzz") in just 8 characters ;-)