help with DATE fields

[ktrinad]

By: emredincer - emredincer

help with DATE fields

2006-06-16 01:12

I have two date fields in mysql(START_DATE,END_DATE) and i need to create a report where i will display records between a given startDate and endDate.I define input controls for these two dates of type date however jasperserver does not accept date formats as mysql the db holds...(YYYY-MM-DD)

 

I can not use texts since i need comparison like(>=startDate AND <=endDate)

 

any suggestions..

best regards

emre dincer

 

 

By: Javy Dreamer - javydreamercsw

RE: help with DATE fields

2006-06-16 10:20

I see no problem at all.

A condition like this one:

 

DATE_TO_BE_COMPARED between DATE($P{START_DATE}) AND DATE($P{END_DATE})

 

For more details:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[JamieDenman]

MySQL does not seem to accept DATE($P{date_as_string}) of any variations of that. I'm having the same problems as loads of others on this site: use JasperServer calendar to select a date and report falls over with SQL error or doesn't find any data due to date format being incorrect in very simple MySQL query.

 

iReports can format a date (even as a string) in a text field but this doesn't work when writing the SQL as there is no access to a "pattern". Have tried the MySQL formatting methods and iReports doesn't seem to like them!!!!

 

Why have the developers made a basic element like date handling so complex that there are hundreds of posts (most without any solutions that work) on this site asking about it?

[lucianc]

JamieDenman wrote:

I'm having the same problems as loads of others on this site: use JasperServer calendar to select a date and report falls over with SQL error or doesn't find any data due to date format being incorrect in very simple MySQL query.

 

Which JasperServer version are you using? Does the report data source specify a timezone?

 

Regards,

Lucian