1

I want to insert a time which is taken from a textbox to the mysql database TIME column. I suppose I need to convert String to TIME like converting String to Date in mysql using "STR_TO_DATE" in the query. I looked for answers but I didn't get the answer I required.

Edit: SQL from comments:

"insert into schedules (
    courseid, 
    batch, 
    subjectid, 
    teacherid, 
    stime, 
    etime, 
    date, 
    location, 
    building, 
    department, 
    hall, 
    status
) values ('" + 
    getCourse() + "','" + 
    getBatch() + "', '" + 
    getSubject() + "','" + 
    getTeacher() + "', '" + 
    getStime()+ "','" + 
    getEtime()+ 
    "',STR_TO_DATE('" + getDate() + "','%d-%m-%Y'),'" + 
    getLocation() + "', '" + 
    getBuilding() + "', '" + 
    getDepartment()+ "', '" + 
    getHall() + 
    "','ACTIVE')"
James Z
  • 12,209
  • 10
  • 24
  • 44
Rukshan Mahendra
  • 113
  • 1
  • 1
  • 7
  • 1
    Mysql accepts simple strings like '05:12:59' into `TIME` type columns. See [docs](https://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html). – piotrm Jun 28 '15 at 05:05
  • Where is your code?? – ganeshvjy Jun 28 '15 at 05:10
  • This is my query: "insert into schedules (courseid, batch, subjectid, teacherid, stime, etime, date, location, building, department, hall, status) values ('" + getCourse() + "','" + getBatch() + "', '" + getSubject() + "','" + getTeacher() + "', '" + getStime()+ "','" + getEtime()+ "',STR_TO_DATE('" + getDate() + "','%d-%m-%Y'),'" + getLocation() + "', '" + getBuilding() + "', '" + getDepartment()+ "', '" + getHall() + "','ACTIVE')" – Rukshan Mahendra Jun 28 '15 at 05:13
  • actually I tried passing a string directly. It gives me this error " com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect time value: 'null' for column 'stime' at row 1 " – Rukshan Mahendra Jun 28 '15 at 05:18
  • Please note that in the above query, "stime" and "etime" are the times which I want to put into the database – Rukshan Mahendra Jun 28 '15 at 05:25

1 Answers1

0

As stated in comments Mysql accepts simple strings like '05:12:59' into TIME type columns but lets try to have another answer to it to. Check the format of date you get from textbox and edit Simple date format. You can try below.

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date parsedDate = dateFormat.parse(request.getParameter("textBoxName"));
Timestamp timestamp = new java.sql.Timestamp(parsedDate.getTime());//or you can assign this stuff to stime variable

I assume you are using preparedStatement as I think you will inserting many times. If so you can set the parameter like this.

preparedStatement.setTimestamp(1, timestamp);//1 is the index of parameter you can choose named parameters too

Also you can choose the to set stime and pass it in query using its relative getter.

Viraj Nalawade
  • 3,137
  • 3
  • 28
  • 44