Hello
I would like to edit database fields in SQL Server of data type DateTimeOffset with SDAC.
When I read these fields it works correctly, depending on the definition, no matter what timezone value is in the database. The local time is always read out (GMT+1 in my case).
- CREATE TABLE [dbo].[MyTable](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [DateTimeOffset] [datetimeoffset](7) NULL);
- INSERT INTO [dbo].[MyTable] ([DateTimeOffset]) VALUES ('2022-12-01 16:00:00 +0:00') -- AsDateTime = 2022-12-01 17:00:00
- INSERT INTO [dbo].[MyTable] ([DateTimeOffset]) VALUES ('2022-12-01 16:00:00 +5:00') -- AsDateTime = 2022-12-01 12:00:00
However, when I want to write the values to the database, the DateTime value is always interpreted as UTC time and ends up in the database without any time zone or conversion.
- query.FieldByName('DateTimeOffset').AsDateTime := System.DateUtils.EncodeDateTime(2022, 12, 01, 17, 00, 00, 000) -- Becomes 2022-12-01 17:00:00.0000000 +00:00, expected: 2022-12-01 17:00:00.0000000 +01:00 or 2022-12-01 16:00:00.0000000 +00:00
I also tried using the SQLTimeStampOffset data type. This is not normally supported.
I added the line Add(ftTimeStampOffset, 'DateTimeOffset', False, False) to the source code in DBAccess TFieldTypeInfos constructor and then adjusted the mapping:
query.DataTypeMap.AddDBTypeRule(msDatetimeoffset, TFieldType.ftTimeStampOffset);
This way I could address the field via AsSQLTimeStampOffset, but the result was the same as with AsDateTime. Again, the time zone was ignored when writing to the database.
Is there a better support for the DateTimeOffset data type planned?