How to deal with datetimeoffset fields

[VincentBergeron]

Hi!

We just bought a Delphi licence of SDAC.

We have many datetimeoffset fields. Ex: 2013-08-22 14:00:00.0000000 -04:00

When reading the values, the correct DateTime is handled by our application.

But how can we handle inserts/updates? Currently, the value is being inserted with 0 as the time offset. Ex: 2013-08-07 10:30:00.0000000 +00:00

We're using a TMSQuery, the SQL statement is generated at each call and we're using params.

Ex:

Query.Sql.Text := 'insert into table (datetimeoffsetfield) values (:param1)';
Query.ParamByName('param1').asDateTime := MyDate;
Query.ExecQuery;

Thanks

[AndreyZ]

Hello,

For the time being, SDAC doesn't support such functionality. We will investigate the possibility of adding such functionality.

[VincentBergeron]

Hi

In History.html, you wrote this:

4.80.0.54 01-Dec-09

• Added support for dbMonitor 3

• Added support of DATETIMEOFFSET field type for SQL Server 2008

• Fixed bug with updating table, that has field name, representing a valid hexadecimal number

• Fixed bug with updating field value with preceding spaces when TrimFixedChar or TrimVarChar = True

• Fixed bug with executing large file in UTF-8 format

• Fixed bug with repeated executing TMSConnection.OpenDatasets

• Fixed bug with processing LargeInt parameters in Delphi 5

That confused me... I thought "support of DATETIMEOFFSET" meant "support of DATETIMEOFFSET" and not "partial support of DATETIMEOFFSET"

[VincentBergeron]

Is there a work around?

I construct the SQL statement at runtime, maybe I can use a function or something to achieve the expected result...

[AndreyZ]

As a workaround, you can use the following code:

Code: Select all

MSQuery.SQL.Text := 'insert into table(datetimeoffsetfield) values(:param1)';
MSQuery.ParamByName('param1').AsString := '2013-08-22 14:00:00.0000000 -04:00';
MSQuery.Execute;

[VincentBergeron]

That's not a suitable workaround... I'll have to change my code to +05:00 when the time will change in october and my app will only works with +4 time zoned customers...

Here's my work around.

Code: Select all

function GetMsSqlDateTimeOffsetString(d: TDateTime) : string;
var
  TZI:TTimeZoneInformation;
  LocalTime, UniversalTime:TSystemTime;
  dUTC: TDateTime;
  offset: TTime;

  dateString: string;
  offsetString: string;
  plusMinus: string;
begin
  GetTimeZoneInformation(tzi);
  DateTimeToSystemTime(d,LocalTime);
  TzSpecificLocalTimeToSystemTime(@tzi,LocalTime,UniversalTime);
  dUTC := SystemTimeToDateTime(UniversalTime);

  offset := d - dUTC;

  if (offset < 0) then
    plusMinus := '-'
  else
    plusMinus := '+';

  DateTimeToString(dateString, 'yyyy-mm-dd hh:nn:ss', d);
  DateTimeToString(offsetString, 'hh:nn', offset);

  result := Format('%s %s%s', [dateString, plusminus, offsetString]);
end;

The function takes the current DateTime, converts it to a UTC DateTime, compares the two to get the offset and generates a datetimeoffset string value. You can then use an .asString param to assign the value.

I post this workaround for other users that maybe needs the same thing as me. Feel free to use it.

[AndreyZ]

Starting from RAD Studio 2010, you can just use the TSQLTimeStampOffset record. Here is a code example:

Code: Select all

ShowMessage(SQLTimeStampOffsetToStr('yyyy-mm-dd hh:nn:ss', DateTimeToSQLTimeStampOffset(Now))); 

Please note that to use this code, you should add the SqlTimSt unit to the USES clauses of your unit.