Convert datetimeoffset to varchar

Question

I've searched everywhere but can't seem to find this info and it's not listed (as far as I can tell) in the MS documentation for CONVERT.

Given the Date/Time/Offset: 1997-12-31 14:53:00.123 +04:30

It's easy to cast it to datetimeoffset(3) as CONVERT( datetimeoffset(3), '1997-12-31 14:53:00.123 +04:30' )

But converting it from a datetimeoffset(3) back to text isn't as simple.

DECLARE @DateTimeOffset datetimeoffset(3) = '1997-12-31 14:53:00.123 +04:30';
DECLARE @StringDTO varchar(255) = CONVERT( nvarchar, @DateTimeOffset, 121 );
SELECT @StringDTO;

The two closest styles I could find were 121 and 127:

• 121 returns 1997-12-31 14:53:00.1230000 +0 which is missing the actual TimeZone value
• 127 returns 1997-12-31T10:23:00.1230000Z which is converting everything to Zulu time.

Does anyone know the conversion style for yyyy-mm-dd hh:mi:ss.fff +zz:zz / 1997-12-31 14:53:00.123 +04:30 please?

I can easily roll my own function but wanted to confirm there wasn't an existing function already in SQL Server before I did that.

Much appreciated.

Answer 1

Thats weird because style 121 works for me. However you can always try format e.g.

DECLARE @DateTimeOffset datetimeoffset(3) = '1997-12-31 14:53:00.123 +04:30';

SELECT
    CONVERT(NVARCHAR, @DateTimeOffset, 121)
    , FORMAT(@DateTimeOffset, 'yyyy-MM-dd HH:mm:ss.fff zzz');

Convert performs better though, so use it if you can.

However looking further at your data the issue lies in the fact that you must actually be using datetimeoffset(6) to have your fractions of a second to 6 decimal places. And at that point you overrun the default nvarchar length of 30.

So specifying a size resolves that.

DECLARE @DateTimeOffset datetimeoffset(6) = '1997-12-31 14:53:00.123 +04:30';

SELECT
    CONVERT(NVARCHAR(255), @DateTimeOffset, 121)
    , FORMAT(@DateTimeOffset, 'yyyy-MM-dd HH:mm:ss.fff zzz');

Of course its best practice to always define the length of a string in SQL Server.