Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

How can I insert time in a database and avoid a java.sql.SQLException error?

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
   </cfquery>

Any ideas how I can get this to insert and query the data?
0
lonnyo
Asked:
lonnyo
  • 2
1 Solution
 
erikTsomikSystem Architect, CF programmer Commented:
what is the datatype for that field is in the database
0
 
lonnyoAuthor Commented:
When I get the error, the datatype is TIME (MySQL Database)
0
 
_agx_Commented:
I do not usually use TIME fields,  I tend to use "datetime" with the cfsqltype="cf_sql_timestamp".  You might try that.

Also, if you run the query directly in MySQL what are the values of in those columns? Is it possible they might contain some invalid values?

0
 
lonnyoAuthor Commented:
Thank you that worked.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now