I have nvarchar values with the following format
10:27:32.357 +03 Aug 31 2022
How to convert those to DatetimeOffset variables in T-SQL?
I have to deal with huge amounts of data, so I want to avoid rebuild the value as segments and then use DATETIMEOFFSETFROMPARTS.
Based on the one example we have, and assuming your dates are always in the format hh:mm:ss.nnn [+-]tzoffset MMM dd yyyy (which is honestly an awful format) you could do something like this:
DECLARE @BadDateString nvarchar(50) = N'10:27:32.357 +03 Aug 31 2022';
SELECT CONVERT(datetimeoffset(3),CONVERT(varchar(10),CONVERT(date,RIGHT(@BadDateString,11)),120)+'T'+REPLACE(LEFT(@BadDateString,16),' ','') + ':00')
I strongly suggest, however, that you fix the problem by having the application that is sending this data in this format as a strongly typed date and time (datetimeoffset) value in the first place; T-SQL's forté isn't strong at string manipulations like like.