java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

Question

I'm having a problem passing some dates with the correct formatting to a query.

I have a view where a column is casted from DATETIME to DATE, so I can disregard the time part and group them just by the date.

CREATE VIEW test_view (date, code_id, dist_id, type, reg, a_code, a_stats, rec_stats) AS
SELECT CAST(sn.notif_date as DATE), code_id, di.codigo, di.type, ac.reg, ac.a_code, cd.check, 
CASE WHEN cd.rec_val = 0 THEN 0 ELSE 1 END
FROM card AS cd, sel_notif AS sn, code_id AS ci, dist_id AS di, ar_code AS ac
WHERE ci.id = sn.id_code 
AND cd.id_sel = sn.id
AND di.id = ci.id_dist
AND sn.sel_date >= DATEADD(DD, -90, GETDATE())
AND di.id = ac.id_dist
AND sn.orig = 'VDO'

I try to query this view using 2 dates created with the code bellow:

Date startDate = new DateTime(start).toDate();
Date endDate = new DateTime(end).toDate();

The start and end variables are received as "2013-01-22". When I try to query, I receive this error:

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

If I change the view not to cast to DATE, I don't get the error, but the results come wrong.

Anyone knows how can I format this date to this specific pattern? I tried using TimeStamp, java.sql.Date, etc, and nothing worked...

Solution

You can either use 'DATE(sn.notif_date)' instead of 'CAST(sn.notif_date as DATE)' for populating Date_Only values in view,

OR

at the time of data selection you can use

'SELECT * FROM yourView GROUP BY DATE(sn.notif_date)'

for grouping by date Only..