JasperReports not converting postgresql timestamp to simple date

Question

I saw this question which is similar to my question, but however the workaround still gave me an error.

I have an sql query string like this in my .jrxml file

<queryString>
        <![CDATA[select sl_no,cast(action_on as date) from action_history]]>
</queryString>

I am using the Eclipse plugin JasperStudio for designing the template and am using PostgreSql for my database. Now this query is fetching data properly as expected.

My action_on is a java.sql.Timestamp type.
<field name="action_on" class="java.sql.Timestamp">
I only want the date like 22/08/15 or 22/08/2015 to be printed and don't want the time to be printed, so when I try casting or using action_on::date , I get 22/08/15 12:00 AM printed with the time always 12:00 AM. What perplexes me more is that when I try this command on my terminal , it doesn't give me the time and only the unformatted date!
Is there a workaround for this?

EDIT
I changed <field name="action_on" class="java.sql.Timestamp"> to <field name="action_on" class="java.lang.String"> and I get the date 2015-08-22 ! Is there a way to now format this?

score 1 · Answer 1 · answered Dec 19 '15 at 11:44

1

I got it resolved myself , so I am posting my solution to my problem . A better solution is obviously welcome!

I converted the field type to String i.e did the EDIT to the question. To format the date you can now use
<![CDATA[select sl_no,to_char(action_on,'dd-MM-yyyy') as my_date from action_history]]>
Don't forget to replace every following instance of declaring or using action_on in your field declarations by my_date !

answered Dec 19 '15 at 11:44

Rameshwar Bhaskaran

345
1
4
16

Thanks, it helped me a lot. – Arjun Oct 13 '17 at 12:51

score 0 · Accepted Answer · answered Dec 19 '15 at 16:47

The other solution is to not format in query (but to format when you output) using pattern attribute on the textField tag

<textField pattern="dd-MM-yyyy">
    <reportElement x="0" y="o" width="100" height="20"uuid="b8baea82-84c4-42fa-bccd-62abc96eeded"/>
    <textElement verticalAlignment="Middle"/>
    <textFieldExpression><![CDATA[$F{action_on}]]></textFieldExpression>
</textField>

The main advantage is that if you export to for example excel the column will be correctly filled with a date object (hence a user of excel can filter/sort/format on the date object).

Accepting the answer for the advantage part ! Thanks :D – Rameshwar Bhaskaran Dec 20 '15 at 08:44 — Rameshwar Bhaskaran, Dec 20 '15 at 08:44

JasperReports not converting postgresql timestamp to simple date

2 Answers2