title: Wrong datetime showing in excel by jasper
body: <p>I am not sure why this is happening but timezone and all settings are correct but the datetime which is in datebase is not getting reflected over report and showing something different.</p>
  
  <p><strong>Note :</strong> This excel report is getting exported by jasper report tool.</p>
  
  <p><strong>jrxml</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"  ?&gt;
  &lt;!-- Created with iReport - A designer for JasperReports --&gt;
  &lt;!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd"&gt;
  &lt;jasperReport
           name="groundingReport"
           columnCount="1"
           printOrder="Vertical"
           orientation="Landscape"
           pageWidth="3600"
           pageHeight="842"
           columnWidth="3600"
           columnSpacing="0"
           leftMargin="0"
           rightMargin="0"
           topMargin="0"
           bottomMargin="0"
           whenNoDataType="AllSectionsNoDetail"
           isTitleNewPage="false"
           isSummaryNewPage="false"
           isIgnorePagination="true"&gt;
  
      &lt;property name="ireport.encoding" value="UTF-8" /&gt;
      &lt;import value="java.util.*" /&gt;
      &lt;import value="net.sf.jasperreports.engine.*" /&gt;
      &lt;import value="net.sf.jasperreports.engine.data.*" /&gt;
      &lt;parameter name="SIMPLE_DATE_FORMAT_WITH_TIME_ZONE" isForPrompting="false" class="com.adesa.framework.report.util.SimpleDateFormatWithTimeZone"&gt;
          &lt;defaultValueExpression &gt;&lt;![CDATA[new com.adesa.framework.report.util.SimpleDateFormatWithTimeZone("MM/dd/yyyy HH:mm:ss a", TimeZone.getTimeZone("US/Eastern"))]]&gt;&lt;/defaultValueExpression&gt;
      &lt;/parameter&gt;
      &lt;parameter name="INSPECTION_PROVIDER" isForPrompting="false" class="java.lang.String"/&gt;
  
      &lt;field name="inspectionOrderedDate" class="java.sql.Timestamp"/&gt;
      &lt;field name="inspectionQCCompleteDate" class="java.sql.Timestamp"/&gt;
      &lt;field name="inspectionCompleteDate" class="java.sql.Timestamp"/&gt;
      &lt;field name="inspectionProvider" class="java.lang.String"/&gt;
  
      &lt;variable name="Test" class="java.lang.String" resetType="Report" calculation="Nothing"&gt;
      &lt;/variable&gt;
          &lt;background&gt;
              &lt;band height="0"  isSplitAllowed="true" &gt;
              &lt;/band&gt;
          &lt;/background&gt;
          &lt;title&gt;
              &lt;band height="50"  isSplitAllowed="true" &gt;
              &lt;/band&gt;
          &lt;/title&gt;
          &lt;pageHeader&gt;
              &lt;band height="75"  isSplitAllowed="true" &gt;
                  &lt;textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                  &lt;textFieldExpression   class="java.lang.String"&gt;&lt;![CDATA[$P{JRXML_DT_FRM}]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
                      &lt;reportElement
                          mode="Transparent"
                          x="162"
                          y="30"
                          width="162"
                          height="15"
                          key="textField-40"
                          stretchType="RelativeToTallestObject"
                          positionType="Float"
                          isPrintWhenDetailOverflows="true"/&gt;
                      &lt;box&gt;                   &lt;pen lineWidth="0.0"/&gt;
                      &lt;topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/&gt;
                      &lt;leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/&gt;
                      &lt;bottomPen lineWidth="0.0" lineColor="#000000"/&gt;
                      &lt;rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/&gt;
                      &lt;textElement verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.lang.String"&gt;&lt;![CDATA[$P{JRXML_DT_TO}]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;staticText&gt;
  
                      &lt;textElement textAlignment="Left" verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection Provider]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
                      &lt;textElement verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.lang.String"&gt;&lt;![CDATA[$P{INSPECTION_PROVIDER}.equals("AUTOVIN LIVE") ? "AutoVIN Live" : $P{INSPECTION_PROVIDER}]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
              &lt;/band&gt;
          &lt;/pageHeader&gt;
          &lt;columnHeader&gt;
              &lt;band height="24"  isSplitAllowed="true" &gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection Provider]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;reportElement
                          mode="Opaque"
                          x="162"
                          y="0"
                          width="162"
                          height="24"
                          backcolor="#CCCCFF"
                          key="staticText-2"/&gt;
                      &lt;box&gt;                   &lt;pen lineWidth="0.0"/&gt;
                      &lt;topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/&gt;
                      &lt;leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/&gt;
                      &lt;bottomPen lineWidth="0.0" lineColor="#000000"/&gt;
                      &lt;rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/&gt;
  &lt;/box&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[VIN]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection Ordered]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection Complete]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[ Days to QC Complete]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection Incomplete]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Incomplete Inspection Reason]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[QC Complete]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection Not Inspected ]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection ID]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
                  &lt;staticText&gt;
  
                      &lt;textElement textAlignment="Center" verticalAlignment="Top"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica-Bold" isBold="true"/&gt;
                      &lt;/textElement&gt;
                  &lt;text&gt;&lt;![CDATA[Inspection Cancelled]]&gt;&lt;/text&gt;
                  &lt;/staticText&gt;
              &lt;/band&gt;
          &lt;/columnHeader&gt;
          &lt;detail&gt;
              &lt;band height="15"  isSplitAllowed="true" &gt;
                  &lt;textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.lang.String"&gt;&lt;![CDATA[$F{inspectionProvider}]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.lang.String"&gt;&lt;![CDATA[$F{vin}]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" pattern="MM/dd/yyyy hh:mm:ss a" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement textAlignment="Center" verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.util.Date"&gt;&lt;![CDATA[new SimpleDateFormat("MM/dd/yyyy hh:mm:ss a").parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionOrderedDate}))]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" pattern="MM/dd/yyyy hh:mm:ss a" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement textAlignment="Center" verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.util.Date"&gt;&lt;![CDATA[new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a").parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionCompleteDate}))]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" pattern="###0;-###0" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement textAlignment="Center" verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.lang.Integer"&gt;&lt;![CDATA[$F{daysToQCComplete} != null ? Integer.valueOf($F{daysToQCComplete}): null]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.lang.String"&gt;&lt;![CDATA[$P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionIncompleteDate})]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.lang.String"&gt;&lt;![CDATA[$F{incompleteInspectionReason}]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
                  &lt;textField isStretchWithOverflow="true" pattern="MM/dd/yyyy hh:mm:ss a" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" &gt;
  
                      &lt;textElement textAlignment="Center" verticalAlignment="Bottom"&gt;
                          &lt;font fontName="Arial" pdfFontName="Helvetica" isItalic="false"/&gt;
                      &lt;/textElement&gt;
                  &lt;textFieldExpression   class="java.util.Date"&gt;&lt;![CDATA[new SimpleDateFormat("MM/dd/yyyy HH:mm:ss a").parse($P{SIMPLE_DATE_FORMAT_WITH_TIME_ZONE}.format($F{inspectionQCCompleteDate}))]]&gt;&lt;/textFieldExpression&gt;
                  &lt;/textField&gt;
  
              &lt;/band&gt;
          &lt;/detail&gt;
  
  &lt;/jasperReport&gt;
  </code></pre>
  
  <p><strong>Date is in oracle db  :</strong></p>
  
  <blockquote>
    <p>02-JAN-18 11.22.09 PM</p>
  </blockquote>
  
  <p><strong>Date is in excel reoprt</strong></p>
  
  <blockquote>
    <p>1/2/2018  6:22:09 PM</p>
  </blockquote>
  
  <p>What is the problem with this I really don't know.</p>
  
  <p>But the timezone is correct from excel report perspective as well as db .
  and the timezone is "US/Eastern" only.</p>
  
  <p>And the columns which are coming wrong are these fields : </p>
  
  <blockquote>
    <p>inspectionQCCompleteDate, inspectionCompleteDate</p>
  </blockquote>
  
  <p>please someone help me to figure this out.</p>