How can I insert time in a database and avoid a java.sql.SQLException error?
Posted on 2009-04-03
I am using coldfusion to create a table that lists the "Calendar of Events". I have a form that collects the STARTTIME and ENDTIME. The data is entered into the form with the following format: hh:mm tt. I now have the INSERT query accepting the data into the database by using:
<cfqueryparam value="#parsedatetime(Form.StartTime)#" cfsqltype="cf_sql_time">, <cfqueryparam value="#parsedatetime(Form.Endtime)#" cfsqltype="cf_sql_time">
I originally was trying to use cfsqltype="cf_sql_date" and a DATETIME setting in the database. I can get the insert into the database to work, but it puts the time in as 00:00:00. When I switched the datatype in the database to TIME and used the code above, I was able to INSERT the data correctly, but now I am getting the following error when I try to query the data:
java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time
My query is:
<cfquery datasource="mainstreet_cafe" name="getAllEvents">
SELECT * FROM calendar_of_events
Any ideas how I can get this to insert and query the data?