[Bug]-[66.66666666666667]:Sql server OffsetDateTime not supported?

[pbjorklund]

Is there an existing issue for this?

• [X] I have searched the existing issues

Current Behavior

Connected Azure SQL server db.

Tried to run a simple select * from users; query.

Got

Java 8 date/time type java.time.OffsetDateTime not supported by default: add Module "com.fasterxml.jackson.datatype:jackson-datatype-jsr310" to enable handling (through reference chain: java.util.ArrayList[0]->java.util.LinkedHashMap["CreatedAt"]->microsoft.sql.DateTimeOffset["offsetDateTime"]) { "requestParams":{...} }

Steps To Reproduce

1. Create sql server db table with column of datatype datetimeoffset
2. Try to query data in appsmith

Environment

Production

Version

Self-Hosted

[pbjorklund]

Any updates?

[rohan-arthur]

@pbjorklund thank you for following up, agree that this should just work. Will check with the team and update you here

[subrata71]

Workaround

@pbjorklund This is definitely an issue. The team will look into this and will get back with a permanent solution. To unblock you, can you please try the following query and let us know if this serves the purpose for now?

SELECT 
    CONVERT(varchar(255), your_offset_datetime_column, 127) ts
FROM your_table

[subrata71]

This is not straightforward to achieve. It will take special handling of the OffsetDateTime to let it be parsed through Jackson. A possible solution could be found in this SO post.

[vaibh1297]

Total users of Ms sql in last months : 499 Assuming 10% of users are affected by this, total count : 50

Stats

Stat	Values
Reach	50
Effort (months)	0.75

[notflip]

Workaround

@pbjorklund This is definitely an issue. The team will look into this and will get back with a permanent solution. To unblock you, can you please try the following query and let us know if this serves the purpose for now?

SELECT 
    CONVERT(varchar(255), your_offset_datetime_column, 127) ts
FROM your_table

This workaround works for me, thanks

[pbjorklund]

Sure, workaround does enable querying.

But means select * will not work for any table in our db since all tables contains offsets.

Will check back in another year or so 👌