Time dimension returns [object Object] values from mssql

[LzbnSam]

Problem
I have a problem with time type with MSSQL source database. In playground I got [object Object] result instead of real dates values.
I am using:

• MSSQL 2019
• Latests Cube Core Docker Image

The generated script works fine and returns values.

My cube yaml:

cubes:
  - name: cube_mes_supply
    sql_table: cube.v_mes_supply
    data_source: dwh_reporting_development

    joins: 
      - name: dim_document_status
        relationship: many_to_one
        sql: "{cube_mes_supply}.ATTB = {dim_document_status.operation_id}"

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true      

      - name: company_guid
        sql: COMPANY
        type: string

      - name: mars_reason
        sql: mars_reason
        type: string

      - name: is_cross_docking
        sql: IS_CROSS_DOCKING
        type: boolean

      - name: create_date
        sql: create_datetime
        type: time
      
      - name: test_datetimeoffset
        sql: test_datetimeoffset
        type: time

    measures:
      - name: count
        type: count

      - name: quantity
        sql: Quantity
        type: sum

      - name: amount_rub
        sql: Amount_Rub
        type: sum

    pre_aggregations:
      - name: marsReasonIsCrossDockingMeasure
        measures:
          - amount_rub
          - quantity
        dimensions:
          - mars_reason
          - is_cross_docking
        refreshKey:
          every: 10 minute

I think that the problem is that MSSQL returns DATETIMEOFFSET type with nanoseconds.
How can I fix that?

[BubuDavid]

Yeap, this is super annoying, I ended up changing to postgress :c, I hope this can be checked for someone..

[prabhusiva]

I am also facing the same issue for Date columns property. i am using a MySql database.

[ngg4686]

I am facing the same issue using an Azure SQL server.

I confirmed on the SQL side that this field is in datetimeoffset(7) as the documentation requires and it appears to recognize that it is time, but inside of Cube it returns [object Object]. It doesn't appear to matter if you cast the value or not to datetimeoffset, time just purely isn't working inside of Cube core.

If I cast it as nvarchar(255) and reassign it to a string i can at least get the data to show up.

[igorlukanin]

@prabhusiva So, I see you're using mysql driver. What is the data type for your date/time column? How is it referenced in the data model?

@ngg4686 @LzbnSam Same goes for you guys. I guess you're using the mssql driver and the data type is DATETIMEOFFSET. I see that it's referenced directly in the data model, without any casts. Which SQL does Cube generate for you?

[zafir83]

same issue but no one reply

[igorlukanin]

@zafir83 If you can provide answers to the questions I raise above—that would be helpful.

[vladik-vitre]

I can confirm this issue occurs specifically with MS SQL Server and is tied exclusively to the deployment type setting.

Technical details:
• When using MS SQL Server with datetime2 fields (defined as type: 'time'), the serialization behavior is inconsistent between deployment types
• In development deployment: datetime values are returned as quoted strings or as objects with { nanosecondsDelta: ... } format
• In production deployment: datetime values are correctly returned as ISO timestamp strings
• This behavior can be verified in SQL Runner - development mode adds double quotes around datetime values, while production mode doesn't
• The playground displays [object Object] in development but proper date strings in production
Important clarification:​ This issue occurs even without any pre-aggregation configuration changes. Simply switching the deployment type from development to production (with all other settings identical) resolves the serialization problem.
This appears to be related to how different deployment types in Cube handle MS SQL Server's datetime2 type serialization, with development mode using a different serialization path that doesn't properly handle these datetime types.
This inconsistency creates significant challenges for applications that need to parse these datetime values consistently across environments, requiring different parsing logic depending on the deployment type.

Has anyone else encountered this with other database types, or is this specifically an MS SQL Server issue?

[igorlukanin]

@vladik-vitre When you were testing this, which versions of Cube were used by your production and development deployments?

[vladik-vitre]

@igorlukanin this happened on cube cloud, latest release, I think 54