iReport Query tool and DATEDIFF

[incarta]

Hello,

I'm am quite new to iReports and am still trying to understand various techniques for creating reports.  I have a specific question that is puzzling me; I have a query that runs in MS SQL, however when I run the same query in the iReport Query tool it fails silently.  This is the query:

SELECT     TOP (100) PERCENT dbo.tblCritCareAdmit.CC_ID, dbo.tblDemographic.PATNT_PASID,
                      dbo.tblDemographic.Pat_Given + ' ' + dbo.tblDemographic.Pat_Family AS Name, dbo.tblCritCareAdmit.Age, dbo.tblCritCareAdmit.CCDateAdmit,
                      dbo.tblCritCareAdmit.CCDateDis, dbo.tblHospEpisode.HosDateDis, dbo.tblHospEpisode.Disch_Loc, dbo.tblPhysiology.Ap2Score,
                      dbo.tblPhysiology.Ap2Rod, dbo.tblHospOutcome.HosOutcome, dbo.tblCCOutcome.CCOutcome,

                      DATEDIFF(day, dbo.tblCritCareAdmit.CCDateAdmit, dbo.tblCritCareAdmit.CCDateDis) AS DaysInICU

FROM         dbo.tblDemographic INNER JOIN
                      dbo.tblHospEpisode ON dbo.tblDemographic.PATNT_PASID = dbo.tblHospEpisode.PATNT_PASID INNER JOIN
                      dbo.tblCritCareAdmit ON dbo.tblHospEpisode.HSPEP_ID = dbo.tblCritCareAdmit.HSPEP_ID LEFT OUTER JOIN
                      dbo.tblCCOutcome ON dbo.tblCritCareAdmit.CCOutcome = dbo.tblCCOutcome.CC_Out_Code LEFT OUTER JOIN
                      dbo.tblHospOutcome ON dbo.tblHospEpisode.HosOutcome = dbo.tblHospOutcome.Hos_Out_ID LEFT OUTER JOIN
                      dbo.tblPhysiology ON dbo.tblCritCareAdmit.CC_ID = dbo.tblPhysiology.CC_ID
WHERE dbo.tblCritCareAdmit.CCOutcome!='Died in ICU'
ORDER BY dbo.tblCritCareAdmit.CCDateAdmit DESC, Name;

The specific issue is the DATEDIFF Function.  If I click the "Read Fields" button in the query designer, it fails to list "DaysInICU" as a field (although "Name" is listed).  Equally, refreshing "Preview Data" fails to list the field although all others are listed as expected.  The Query functions as expected in the MS Query tool against the same server.

Apologies, if I am missing something here, but any assistance would be much appreicated.  I am using iReports 4.7.1.

[mespub]

Hello,

Did you check the version of the jdbc driver ?

 

Regards