Format datetimeoffset columns based on precision and offset - Databases

TopAnswers Databases

Meta

sql-server dbfiddle add tag

Josh Darnell

It would be nice if fiddles showed the *offset* portion of a `datetimeoffset` column when displaying resultsets, similar to how SSMS displays them.  Additionally, I'd like to see the defined precision reflected in the output.

Compare this example fiddle and output:

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=415c0ab5f8a75ef30bc0be5fe474160d

Which at the time of this request shows `2020-11-03 13:50:00.0000000` for both columns.

![Screenshot 2020-11-05 at 08.34.11.png](/image?hash=8a8015e06089b8ff18b847bdbd26b5f294016c9d844c5c3f02ae880b9a3fd1ea)

To what is displayed in SSMS:

![ssms-results-offset-precision.png](/image?hash=db34a4f4916e1b1a482754dd9b77d9c339a94161654aa807a776b70044163d5b)

Notice:

- the fiddle shows fractional seconds out to 7 places for both columns, while SSMS shows 0 and 7 fractional seconds based on the data type of the column
- the fiddle does not show the time zone offset ("-04:00") on either column, while SSMS shows it on both

Top Answer

Jack Douglas

I think this was requested before and I wasn't able to figure it out at the time. I finally figured out that we need to call [`GetSchemaTable`](https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.getschematable?redirectedfrom=MSDN&view=dotnet-plat-ext-3.1#System_Data_SqlClient_SqlDataReader_GetSchemaTable), and that the precision is stored in the `NumericScale` field, despite the misleading text in the docs:

> If `ProviderType` is a numeric data type, this is the maximum precision of the column. The precision depends on the definition of the column. If `ProviderType` is not a numeric data type, this is 255.

The offset is now visible for `datetimeoffset` columns, and the correct number of fractional second precision digits displays for `datetimeoffset`, `datetime2` and `time` columns:

<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=415c0ab5f8a75ef30bc0be5fe474160d

The fix is in for all currently supported versions of SQL Server.

1 Answer