java.sql.SQLException: ORA-01830: date format picture ends before converting entire input string

[Farakh khan]

Hello,

Here is oracle SQL prompt that ran this query fine:

SQL> select  count(*) from appointments
  2  where dept_id=2 and s_id=22 and aTime=to_date('08:00','HH24:MI');

  COUNT(*)
----------
         1

SQL>

But in my code its throwing the following error:

java.sql.SQLException: ORA-01830: date format picture ends before converting entire input string

Her is my code:

		String query =  " select  count(*) from appointments "+
				" where dept_id=? and aTime=to_date(?,'HH24:MI') and s_id=? ";


		int exist = 0;
		PreparedStatement ps=cnn.prepareStatement(query);
		ps.setString(1, deptId);
		ps.setString(2, date);
		ps.setString(3, shiftId);

		rs=ps.executeQuery();

		if(rs.next()){		
		exist = rs.getInt(1);
		}
		ps.close();

Best regards

[Sudheer Bhat]

Can you print the value of date variable? The error message clearly says that input string is larger than the provided date format.
You need to make sure that your string should contain exactly 5 characters. For eg 08:00.

[Farakh khan]

The date is 7/30/2011 and has time 14:45

[Sudheer Bhat]

You need to strip off the 7/30/2011 part in your date variable and the to_date function should only get 14:45.

[Farakh khan]

You need to strip off the 7/30/2011 part in your date variable and the to_date function should only get 14:45.

How?

[Jan Cumps]

You could use java.lang.String.substring(int, int)