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; } }`