Caused by: java.sql.SQLException: Year out of range.

Thanks sikandard., I have posted this question as a separate post and was able to fix the issue with help .. May be useful for people who are facing this issue forum.appian.com/.../e-164135

Hi sikandard,

I've also had this problem with the date range search. Oddly, I have 5 date ranges for different fields that can be searched on. They are all checked for null, and converted with todate() if not null.

The first two sets work and return data. The other three sets result in a 500 error.

I'll continue looking into it, but this inconsistent behaviour is frustrating.

Ok I've figured out that it gives the 500 error when the DB field that is being searched is of type date. It works fine for timestamp though - oracle if interested.

Hi sikandard .. I am facing the same issue now because of passing datetime as json .. I tried converting json text back to datetime but it din work ..still getting 500 error .. could you please put your code here for reference .. Both parent and child rue ..

Hi Sikandard .. Could you please paste your code here for reference .. I am not able to resolve the issue yet .. Pasting my code here ..

Rule: Report_HomeScreen

local!datasubsetNew_any: rule!Report_getDataSubset(
fromDateTime_dtm: local!fromDateTime_dtm,
toDateTime_dtm: local!toDateTime_dtm,
pagingInfo_any: a!pagingInfo(startIndex: 1, batchSize: -1)
),
local!exportUri: getdatasubsetdownloadlinkfromrule(
rule!Report_getExportableDataSubset,
a!toJson(
{
fromDateTime_dtm: local!fromDateTime_dtm,
toDateTime_dtm: local!toDateTime_dtm
}
)
)



Rule: Report_getExportableDataSubset

with(
/*-- ri!input of type TEXT --*/
local!searchParam: a!fromJson(
ri!input
),
/*-- Forming DataSubset again instead of passing as JSON parameter--*/
local!datasubsetNew_any: rule!Report_getDataSubset(
fromDateTime_dtm: local!searchParam.fromDateTime_dtm,
toDateTime_dtm: local!searchParam.toDateTime_dtm,
pagingInfo_any: a!pagingInfo(startIndex: 1, batchSize: -1)
),
'type!{urn:appian:plugin:datasubsetdownload:types}ExportableDataSubset'
(
datasubset: local!datasubsetNew_any,
fieldNames: {"requestStatus","requestId","ageOfStatus"},
fieldLabels: {"STATUS OF CDD","COUNT","AGING IN DAYS"},
filename: "AgingReport"
)
)

Here whenever I pass the values of type DateTime as JSON and try to access them in child rule, I am getting 500 Error .. Also tried date time conversion (pasted snippet below) as you suggested but no luck ..

local!exportUri: getdatasubsetdownloadlinkfromrule(
rule!Report_getExportableDataSubset,
a!toJson(
{
fromDateTime_dtm: todatetime(local!fromDateTime_dtm),
toDateTime_dtm: todatetime(local!toDateTime_dtm)
}
)
)

I believe it works for Date type and not for Timestamp, can you look at the error log to find the details, I created a new field in database to store the date and then filtered on this field. Hope this helps.

Hey kumaraveln. Before converting your dates and datetimes tojson(), you must tostring() them. Then after converting your back fromjson(), you must todate() or todatetime() them. That should fix your issue.

Sorry for the late reply. Converting the date to string before passing as JSON worked. Will paste the code here for reference, shortly.

Attaching the working code .. Let me know if anyone face any issue ..

Report_ExportExcel_Code.txt