Subtract dates

gerramirez · February 24, 2017

In my report I have two dates and I want to get the difference in days. you cant help me.

I HAVE THIS EXPRESSION IN TEXT FIELD ESPRESSION BUT DON'T WORK, IT SHOW ME AS NULL

($V{TEST1}.getTime() - $V{TEST2}.getTime())/1000/60/60

robsil · February 24, 2017

Are the dates in the DB? If there are two columns in your DB you can use the SQL function "DATEDIFF" to creat e a column that will have the days diff you require. If not, you can use an expression... it all depends on what class the dates are.

Assuming they are a Date, you can use $F{DateOne}.getTime()-$F{DateTwo}.getTime() which will return the milliseconds...

to convert to days:

($F{DateOne}.getTime()-$F{DateTwo}.getTime()) /(24*60*60*1000)

gerramirez · February 25, 2017

I HAVE THIS EXPRESSION IN TEXT FIELD ESPRESSION BUT DON'T WORK, IT SHOW ME AS NULL

($V{TEST1}.getTime() - $V{TEST2}.getTime())/1000/60/60

hozawa · February 26, 2017

Not sure why it doesn't work for you. Working OK with me.

<?xml version="1.0" encoding="UTF-8"?>

<![CDATA[]]>

</queryString>

</variable>

</variable>

</background>

<title>

</textField>

</textField>

</textField>

</band>

</title>

</pageHeader>

</columnHeader>

</detail>

</columnFooter>

</pageFooter>

</summary>

</jasperReport>

gerramirez · February 27, 2017

your xml is well work with me too.

but

I work in ireport 5.6 and I send you my xml, my connection is to iSeries can you tell me your opinion please. tank you very much

<?xml version="1.0" encoding="UTF-8"?>

</parameter>

<![CDATA[sELECT

FRPS."PSPN" AS FRPS_PSPN,

FRPS."PSCODSIT" AS FRPS_PSCODSIT,

FRNM."NMDESIGN" AS FRNM_NMDESIGN,

FRAV."AVIMMATR" AS FRAV_AVIMMATR,

FRPS."PSTSN" AS FRPS_PSTSN,

FRPS."PSTSO" AS FRPS_PSTSO,

FRPS."PSSN" AS FRPS_PSSN,

FRPS."PSCSN" AS FRPS_PSCSN,

FRPS."PSCSO" AS FRPS_PSCSO,

FRPS."PSJSN" AS FRPS_PSJSN,

FRPS."PSJSO" AS FRPS_PSJSO,

FRAV."AVHVAB" AS FRAV_AVHVAB,

FRAV."AVCY" AS FRAV_AVCY,

FRKX."AVHVAB" AS FRKX_AVHVAB,

FRKX."AVCY" AS FRKX_AVCY,

FRPS."PSTSI2" AS FRPS_PSTSI2,

FRPS."PSTSI" AS FRPS_PSTSI,

FRPS."PSJSI2" AS FRPS_PSJSI2,

FRPS."PSJSI" AS FRPS_PSJSI,

FRPS."PSCSI2" AS FRPS_PSCSI2,

FRPS."PSCSI" AS FRPS_PSCSI,

FRKX."KXFIN" AS FRKX_KXFIN,

FRKX."KXPOSAV" AS FRKX_KXPOSAV,

FRKX."KXTYPE" AS FRKX_KXTYPE,

FRPS."PSA4" AS FRPS_PSA4,

FRPS."PSMM" AS FRPS_PSMM,

FRPS."PSJJ" AS FRPS_PSJJ,

FRAV."AVCRMA4" AS FRAV_AVCRMA4,

FRAV."AVCRMMM" AS FRAV_AVCRMMM,

FRAV."AVCRMJJ" AS FRAV_AVCRMJJ

FROM

"AMSF"."FRNM" FRNM INNER JOIN "AMSF"."FRPS" FRPS ON FRNM."NMPN" = FRPS."PSPN"

LEFT OUTER JOIN "AMSF"."FRAV" FRAV ON FRPS."PSNUMAP" = FRAV."AVNUMAP"

LEFT OUTER JOIN "AMSF"."FRKX" FRKX ON FRPS."PSPN" = FRKX."PSPN"

AND FRKX."PSSN" = FRPS."PSSN"

WHERE

FRPS."PSCODSIT" = '41'

AND FRAV."AVACTIF" = 'O'

AND FRKX."KXTYPE" = 'O/C'

AND FRAV."AVIMMATR" = $P{AVION}

ORDER BY

FRAV."AVIMMATR" ASC]]>

</queryString>

</variable>

</variable>

</variable>

</variable>

<title>

<box>

</box>

</textElement>

</staticText>

</band>

</title>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

<box>

</box>

</textElement>

</staticText>

</band>

</columnHeader>

<box>

</box>

</textElement>

</textField>

<box>

</box>

</textElement>

</textField>

<box>

</box>

</textElement>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSTSO}.intValue()==999995 ? "UNK" :

($F{FRPS_PSTSO}.intValue()==999996 ? "C/M" :

($F{FRPS_PSTSO}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSTSO}.intValue()==999998 ? "O/C" :

($F{FRPS_PSTSO}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSTSO}+($F{FRAV_AVHVAB}-$F{FRKX_AVHVAB})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSCSO}.intValue()==999995 ? "UNK" :

($F{FRPS_PSCSO}.intValue()==999996 ? "C/M" :

($F{FRPS_PSCSO}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSCSO}.intValue()==999998 ? "O/C" :

($F{FRPS_PSCSO}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSCSO}+($F{FRAV_AVCY}-$F{FRKX_AVCY})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSJSO}.intValue()==999995 ? "UNK" :

($F{FRPS_PSJSO}.intValue()==999996 ? "C/M" :

($F{FRPS_PSJSO}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSJSO}.intValue()==999998 ? "O/C" :

($F{FRPS_PSJSO}.intValue()==999999 ? "N/FLW" :

$F{FRPS_PSJSO}

)))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSTSN}.intValue()==999995 ? "UNK" :

($F{FRPS_PSTSN}.intValue()==999996 ? "C/M" :

($F{FRPS_PSTSN}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSTSN}.intValue()==999998 ? "O/C" :

($F{FRPS_PSTSN}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSTSN}+($F{FRAV_AVHVAB}-$F{FRKX_AVHVAB})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSJSN}.intValue()==999995 ? "UNK" :

($F{FRPS_PSJSN}.intValue()==999996 ? "C/M" :

($F{FRPS_PSJSN}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSJSN}.intValue()==999998 ? "O/C" :

($F{FRPS_PSJSN}.intValue()==999999 ? "N/FLW" :

$F{FRPS_PSJSN}

)))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSCSN}.intValue()==999995 ? "UNK" :

($F{FRPS_PSCSN}.intValue()==999996 ? "C/M" :

($F{FRPS_PSCSN}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSCSN}.intValue()==999998 ? "O/C" :

($F{FRPS_PSCSN}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSCSN}+($F{FRAV_AVCY}-$F{FRKX_AVCY})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSTSI2}.intValue()==999995 ? "UNK" :

($F{FRPS_PSTSI2}.intValue()==999996 ? "C/M" :

($F{FRPS_PSTSI2}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSTSI2}.intValue()==999998 ? "O/C" :

($F{FRPS_PSTSI2}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSTSI2}+($F{FRAV_AVHVAB}-$F{FRKX_AVHVAB})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSTSI}.intValue()==999995 ? "UNK" :

($F{FRPS_PSTSI}.intValue()==999996 ? "C/M" :

($F{FRPS_PSTSI}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSTSI}.intValue()==999998 ? "O/C" :

($F{FRPS_PSTSI}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSTSI}+($F{FRAV_AVHVAB}-$F{FRKX_AVHVAB})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSCSI2}.intValue()==999995 ? "UNK" :

($F{FRPS_PSCSI2}.intValue()==999996 ? "C/M" :

($F{FRPS_PSCSI2}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSCSI2}.intValue()==999998 ? "O/C" :

($F{FRPS_PSCSI2}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSCSI2}+($F{FRAV_AVCY}-$F{FRKX_AVCY})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSCSI}.intValue()==999995 ? "UNK" :

($F{FRPS_PSCSI}.intValue()==999996 ? "C/M" :

($F{FRPS_PSCSI}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSCSI}.intValue()==999998 ? "O/C" :

($F{FRPS_PSCSI}.intValue()==999999 ? "N/FLW" :

($F{FRPS_PSCSI}+($F{FRAV_AVCY}-$F{FRKX_AVCY})

))))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSJSI2}.intValue()==999995 ? "UNK" :

($F{FRPS_PSJSI2}.intValue()==999996 ? "C/M" :

($F{FRPS_PSJSI2}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSJSI2}.intValue()==999998 ? "O/C" :

($F{FRPS_PSJSI2}.intValue()==999999 ? "N/FLW" :

$F{FRPS_PSJSI2})

))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

<textFieldExpression><![CDATA[($F{FRPS_PSJSI}.intValue()==999995 ? "UNK" :

($F{FRPS_PSJSI}.intValue()==999996 ? "C/M" :

($F{FRPS_PSJSI}.intValue()==999997 ? "UNLIMIT" :

($F{FRPS_PSJSI}.intValue()==999998 ? "O/C" :

($F{FRPS_PSJSI}.intValue()==999999 ? "N/FLW" :

$F{FRPS_PSJSI})

))))]]></textFieldExpression>

</textField>

<box>

</box>

</textElement>

</textField>

<box>

</box>

</textElement>

</textField>

<box>

</box>

</textElement>

</textField>

</textField>

</band>

</detail>

</jasperReport>

Sign In

Subtract dates

Recommended Posts

gerramirez

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

robsil

Link to comment

Share on other sites

gerramirez

Link to comment

Share on other sites

hozawa

Link to comment

Share on other sites

gerramirez

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Activity

Products

Explore