Page 1 of 1
TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Mon 11 Aug 2014 09:17
by martin.trummer
for MySQL the way to insert microseconds was to specify ftString for the Column and then add a string value like this: '2014-05-20 15:49:14.000000'
the same trick does not work with SQL Server.
according to the SQL server doc's the column type datetime(2) can support a fractional second precision up to 7 digits. And according to some other doc-page, SQL server supports ISO 8601 compliant date-time literals:
http://msdn.microsoft.com/en-us/library/bb677335.aspx
I've tested this in the SQL Server Management Studio the following query works-column data type is datetime2(6):
Code: Select all
insert into [Test]
values
('2014-08-08T14:31:11.12345')
but in my test-code for TUniLoader it does not work:
- FAIL: Sender.PutColumnData(0, I, '01-01-2010T01:00:00');
- FAIL: Sender.PutColumnData(0, I, '01-01-2010 01:00:00.123');
- this is okay: Sender.PutColumnData(0, I, '01-01-2010 01:00:00');
so how can we store a date/time value with microsecond precision to SQL Server?
I'm using version 5.3.9 and SQL Server 2014.
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Thu 14 Aug 2014 09:05
by azyk
TDALoader works with SQL Server via the OLE DB interface, that requires each value to be transmitted to server in the data type, that corresponds to the field of the table.
To solve the issue, try to transmit the already converted value (instead of the T-SQL-like value) to the TDALoader.PutColumnData method, for example:
Code: Select all
procedure TMainForm.UniLoaderPutData(Sender: TDALoader);
var
MyDateTime : TDateTime;
begin
MyDateTime := EncodeDateTime(2010, 1, 1, 1, 0, 0, 123);
Sender.PutColumnData(0, 1, MyDateTime);
end;
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Thu 14 Aug 2014 09:10
by martin.trummer
I cannot use this, because we also need sub-millisecond accuracy and the delphi TDateTime type only supports milliseconds
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Mon 18 Aug 2014 13:19
by azyk
To solve the issue, in TUniLoader set the value for the TDAColumn.FieldType property in ftWideString for the field with the datetime2 datatype. For example:
Code: Select all
UniLoader.Columns[0].FieldType := ftWideString;
In this case, to transmit the date string value in the field with the datetime2 datatype, OLEDB is required to use the following format 'yyyy-mm-dd hh:mm:ss[.fffffff]' in the TDALoader.PutColumnData method. For example:
Code: Select all
procedure TMainForm.UniLoaderPutData(Sender: TDALoader);
begin
Sender.PutColumnData(0, 1, '2010-01-01 01:00:00.123');
end;
For more information on string formats for converting into the datetime2 datatype, refer to the MSDN specifications:
http://msdn.microsoft.com/en-us/library/bb677303.aspx
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Mon 18 Aug 2014 13:36
by martin.trummer
thanks a lot.
Actually I had year-month-day in the wrong order. Now it works (also with field type ftString).
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Mon 18 Aug 2014 15:01
by azyk
Feel free to contact us if you have any further questions.
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Thu 11 Dec 2014 18:05
by martin.trummer
some update on this:
one a customer PC the date-time with millis for SQL Server failed:
Code: Select all
EMSError: Conversion failed when converting date and/or time from character string.
Exception: Conversion failed when converting date and/or time from character string.
the problem was, that the native OLEDBProvider for SQL server was not installed.
The default OLEDBProvider setting for a SQL Server connection is prAuto (see
UniDac Help).
Thus prSQL (which has limited functionality and does not support microseconds) was used on the customer PC which caused the error.
On our development and test-machines we had installed the full SQL Server client (including the native OLEDBProvider) - thus it worked for our tests.
Here's a link
how to install only the SQL Server native client on a customers PC.
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Fri 12 Dec 2014 17:26
by azyk
We have tried to reproduce the problem for OLE DB Provider according to your recommendations, but it wasn't reproduced. Please try to compose a small sample to demonstrate the problem and send it to andreyz*devart*com, including the tables creating script.
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Mon 29 Dec 2014 15:00
by azyk
We have received your test sample and fixed this problem. This fix will be included in the next UniDAC build.
Re: TUniLoader and DateTime with milliseconds [SQL Server]
Posted: Fri 21 Aug 2015 12:24
by martin.trummer
verified that the bug is fixed in V6.1.6