Yogendra Singh
Reputation: 553
mysql datetime format change using java for JasperReports generation
I am using JasperReport's iReport 4.5.0 designer to build my report. I am having one problem with the timestamp fields startDate and endDate. In my designer I changed the format of startDate and endDate using pattern.
But, the problem is that when I export the report as .excel or .pdf it still gives me date in timestamp format, not the format YYYY-MM-ddI had done in pattern.
my report generated with starDate as 2012-03-01 00:00:00.0 but I want my excel report to be have starDate in 2012-03-01 like YYYY-MM-dd.
Any solution you had, I don't want to change my database type from datetime to date.
My projectmain.jrxml file code is below mentioned:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="projectmain" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<import value="java.util.Date"/>
<parameter name="pagelimit" class="java.lang.Integer">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<parameter name="pagestart" class="java.lang.Integer">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[select * from project limit $P{pagestart},$P{pagelimit}]]>
</queryString>
<field name="projectid" class="java.lang.Integer"/>
<field name="enddate" class="java.sql.Timestamp"/>
<field name="projectdesc" class="java.lang.String"/>
<field name="projectname" class="java.lang.String"/>
<field name="projecttitle" class="java.lang.String"/>
<field name="startdate" class="java.sql.Timestamp"/>
<background>
<band/>
</background>
<title>
<band height="56">
<staticText>
<reportElement x="0" y="0" width="410" height="56"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Century" size="24"/>
</textElement>
<text><![CDATA[Project Management Report Generation]]></text>
</staticText>
</band>
</title>
<pageHeader>
<band height="28">
<staticText>
<reportElement mode="Opaque" x="0" y="0" width="555" height="28" forecolor="#FFFFFF" backcolor="#666666"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Century" size="16" isBold="true"/>
</textElement>
<text><![CDATA[Project Management Report Generation]]></text>
</staticText>
</band>
</pageHeader>
<columnHeader>
<band height="29">
<staticText>
<reportElement mode="Opaque" x="0" y="0" width="144" height="29" backcolor="#CCCCCC"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Middle">
<font fontName="Century" size="16"/>
</textElement>
<text><![CDATA[Project Title]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="144" y="0" width="158" height="29" backcolor="#CCCCCC"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Middle">
<font fontName="Century" size="16"/>
</textElement>
<text><![CDATA[Project Name]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="302" y="0" width="151" height="29" backcolor="#CCCCCC"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Middle">
<font fontName="Century" size="16" isUnderline="false"/>
</textElement>
<text><![CDATA[Start Date]]></text>
</staticText>
<staticText>
<reportElement mode="Opaque" x="453" y="0" width="102" height="29" backcolor="#CCCCCC"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Middle">
<font fontName="Century" size="16"/>
</textElement>
<text><![CDATA[End Date]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="29">
<textField>
<reportElement x="0" y="0" width="144" height="29"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Bottom">
<font fontName="Century" size="12" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{projecttitle}]]></textFieldExpression>
</textField>
<textField pattern="d/M/yyyy">
<reportElement x="453" y="0" width="102" height="29"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Bottom">
<font fontName="Century" size="12" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{enddate}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="144" y="0" width="158" height="29"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Bottom">
<font fontName="Century" size="12" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{projectname}]]></textFieldExpression>
</textField>
<textField pattern="d/M/yyyy">
<reportElement x="302" y="0" width="151" height="29"/>
<box>
<leftPen lineWidth="0.25"/>
<bottomPen lineWidth="0.25"/>
<rightPen lineWidth="0.25"/>
</box>
<textElement verticalAlignment="Bottom">
<font fontName="Century" size="12" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{startdate}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter>
<band height="60">
<textField pattern="MMMMM dd, yyyy">
<reportElement mode="Opaque" x="0" y="20" width="555" height="20" forecolor="#000000" backcolor="#CCCCCC"/>
<textElement verticalAlignment="Middle">
<font fontName="Calibri" size="14" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
</textField>
</band>
</columnFooter>
<pageFooter>
<band height="31">
<staticText>
<reportElement x="0" y="1" width="541" height="30"/>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Century" size="10" isBold="false"/>
</textElement>
<text><![CDATA[Copyright © Project Management 2012 | Design by Kintudesigns.com]]></text>
</staticText>
</band>
</pageFooter>
<summary>
<band height="42"/>
</summary>
</jasperReport>
Upvotes: 4
Views: 5325
Answers (1)
Alex K
Reputation: 22857
You can try to set net.sf.jasperreports.export.xls.detect.cell.type property for correct formatting in case export to Excel format.
This issue is very strange. You can add the variable of java.text.DateFormat type and use it like in this sample:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="datetime_format" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
<queryString>
<![CDATA[SELECT date1, date2 FROM table]]>
</queryString>
<field name="date1" class="java.sql.Timestamp"/>
<field name="date2" class="java.sql.Timestamp"/>
<variable name="dateFormat" class="java.text.DateFormat" resetType="None">
<variableExpression><![CDATA[new SimpleDateFormat("yyyy-MM-dd")]]></variableExpression>
</variable>
<detail>
<band height="20" splitType="Stretch">
<textField>
<reportElement x="11" y="0" width="194" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$V{dateFormat}.format($F{date1})]]></textFieldExpression>
</textField>
<textField pattern="yyyy-MM-dd">
<reportElement x="205" y="0" width="272" height="20"/>
<textElement/>
<textFieldExpression><![CDATA[$F{date2}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>
It is works for me. I have the correct data for both textFields (with using DateFormat and without it) for PDF and XLS.
Upvotes: 6
Related Questions
- How to display date in HH:mm:ss format in JasperReports?
- Date locale in JasperReports
- Timezone conversion and date formatting in jasper reports?
- Formatting Date on Java
- how to format date and time from JDateChooser to mysql datetime column
- Java DynamicReports format dates regarding to locale
- Creating report in java with JDBC and trying to convert between sql date and java util date
- Jasper Reports Date doesn't change according to the given format
- Joda DateTime,, Formatting, and Mysql Timestamp
- Java Format Custom Date to MySQL DATETIME