Time dimension 'Timestamp' cause SQL error on SQL Server in mondrian 4

[aviera98]

In my cube i have a time dimension with attributes 'Date', 'Month', 'Week', and 'Year', also i am using ms sql server as datasource.

The next MDX query works fine:

SELECT { [Measures].AllMembers } ON COLUMNS,
{ [Time].[Month].members } ON ROWS FROM MyCube

but when i use year on rows causes this error:

mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while loading segment; sql=[select "Time"."Year" as "c0", max("Time"."IHId") as "m0" from "PRD"."Time" as "Time" where "Time"."Year" = TIMESTAMP '2019-01-01 00:00:00.0' group by "Time"."Year"]
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:992)
at mondrian.olap.Util.newInternal(Util.java:2543)
at mondrian.olap.Util.newError(Util.java:2559)
at mondrian.rolap.SqlStatement.handle(SqlStatement.java:363)
at mondrian.rolap.SqlStatement.execute(SqlStatement.java:262)
at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:346)
at mondrian.rolap.agg.SegmentLoader.createExecuteSql(SegmentLoader.java:633)
at mondrian.rolap.agg.SegmentLoader.loadImpl(SegmentLoader.java:195)
at mondrian.rolap.agg.SegmentLoader$SegmentLoadCommand.call(SegmentLoader.java:166)
at mondrian.rolap.agg.SegmentLoader$SegmentLoadCommand.call(SegmentLoader.java:140)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '2019-01-01 00:00:00.0'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:693)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:205)
at mondrian.rolap.SqlStatement.execute(SqlStatement.java:202)

I think it's related to the standard sql query generated, TIMESTAMP 'date' not exists in ms sql server.

Looking on internet i saw this issue fixed on mondrian 8.3... but i need to use mondrian 4.

[suxiaoyan521]

i think you can do like this:
in SqlStatement.java row 410, modify this
return (Comparable) resultSet.getObject(columnPlusOne);
to :
`Object object = resultSet.getObject(columnPlusOne);
if (object instanceof TIMESTAMP){
Timestamp timestamp = DBCountUtil.getOracleTimestamp(object);
return timestamp;
}
return (Comparable) object;

public static Timestamp getOracleTimestamp(Object value) {
try {
Class clz = value.getClass();
Method m = clz.getMethod("timestampValue");
return (Timestamp) m.invoke(value);
} catch (Exception e) {
return null;
}
}`