Date Conversion

[swapna_gelle]

I am trying to create "Date" custom field in Jasper studio.

How do i caluclate date difference in sql query in jasper?

I tried this way, but its not working

For example datediff(dd,open_date,getdate())..

Open date is stored in timestamp format.

Please help me.

 

[hozawa]

SQL syntax depends on the database you are using. If you are using MySQL, the command to get the difference between dates is datediff.

https://www.w3schools.com/sql/func_mysql_datediff.asp

Use FROM_UNIXTIME function to convert from timestamp to date.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

 

[dohungnd94]

You can use CAST ( your_date as DATE) to convert your_date to DATE format. Then, in a field, you can write :

$F{closed_date} - $F{open_date}. It will display the day difference between. Hope it works well, cheers ^^