swapna_gelle Posted June 26, 2017 Share Posted June 26, 2017 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 workingFor example datediff(dd,open_date,getdate())..Open date is stored in timestamp format.Please help me. Link to comment Share on other sites More sharing options...
hozawa Posted June 26, 2017 Share Posted June 26, 2017 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.aspUse 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 Link to comment Share on other sites More sharing options...
dohungnd94 Posted June 27, 2017 Share Posted June 27, 2017 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 ^^ Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now