Page 1 of 1
How to deal with datetimeoffset fields
Posted: Tue 06 Aug 2013 20:38
by 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
Re: How to deal with datetimeoffset fields
Posted: Wed 07 Aug 2013 08:59
by AndreyZ
Hello,
For the time being, SDAC doesn't support such functionality. We will investigate the possibility of adding such functionality.
Re: How to deal with datetimeoffset fields
Posted: Wed 07 Aug 2013 14:24
by 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"
Re: How to deal with datetimeoffset fields
Posted: Wed 07 Aug 2013 16:15
by 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...
Re: How to deal with datetimeoffset fields
Posted: Thu 08 Aug 2013 07:39
by 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;
Re: How to deal with datetimeoffset fields
Posted: Thu 08 Aug 2013 16:47
by 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.
Re: How to deal with datetimeoffset fields
Posted: Fri 09 Aug 2013 05:21
by 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.