[SOLVED] SQL datetime2(7)

[vertnik]

How to read the data from field with type datetime2(7) from SQL table?

The following code is not working:

var td: TDateTime;
td:=SQLQuery.FieldByName('field name').AsDateTime;

However, it works if the same field in table is defined as datetime. 

[vertnik]

I was trying to read this field as string and got the following data:

'2017-06-01 10:57:48.3814412'

I can convert this string to TDateTime variable, but without fractional seconds (3814412) after the dot.

It is possible to convert this string to TDateTime in Lazarus with fractional seconds?

[Thaddy]

That is possible to store and read back in *any* database engine and FPC, provided it gets stored in the full format.
This is not a shortcoming of Lazarus, it is a limitation of the database engine you use.

[vertnik]

Thaddy, thank you. Can you be more specific about database engine. I am using ODBC connection in the
Lazarus for connecting to MS SQL server database.

Please, can you give one example, how can I read from table the data with type "datetime2(7)",
for example '2017-06-01 10:57:48.3814412'.

Like I mentioned in previous post, the solution for now is to read this field type as string and then
convert in Lazarus with StrToDateTime(str,fmt) routine, where "str" is retrieved string from SQL table and "fmt" format
settings for conversation. But I don't know how can I define in fmt the character for fractional seconds, the 7 digits after
the dot.

[rvk]

I'm not sure how many digits the TDateTime can handle.

But the StrToDateTime() only works up to 3 digits for msec.
("Now" also only returns 3 digits msec)

So:

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   S: String;
4.   Dt: TDateTime;
5.   Format: TFormatSettings;
6. begin
7.   S := '2017-06-01 10:57:48.3814412'; // this doesn't work
8.   S := '2017-06-01 10:57:48.381'; // this works
9.  
10.   Format.DateSeparator := '-';
11.   Format.ShortDateFormat := 'yyyy-mm-dd';
12.   Format.TimeSeparator := ':';
13.   Format.LongTimeFormat := 'hh:nn:ss.z';
14.   Format.DecimalSeparator := '.';
15.   Dt := StrToDateTime(S, Format);
16.  
17.   ShowMessage(DateTimeToStr(Dt, Format));
18.  
19. end;

I think you need a new datatype like TDateTime2 (like it's in MSSQL) with higher msec precision.

[howardpc]

I'm not sure how many digits the TDateTime can handle.

TDateTime is an alias for double, which offers a minimum of 15 significant digits.
Since 1 second is approximately 0.0000115 of a day the eight-byte TTime format has potentially five or possibly six significant digits for milliseconds. It is just that StrToDateTime does not utilise them all, presumably because most datetimes need quite a few significant digits for the date, never mind the time component.
If you used a separate TDate for the date and TTime for the time (to allocate all the significant digits of TTime to time), it should be possible to roll your own StrToDateTime() that squeezed a few more significant digits out of the format so it could track nanoseconds. But I don't have the motivation to try...

[LacaK]

I am using ODBC connection in the Lazarus for connecting to MS SQL server database.

Which version of ODBC driver do you use ? I think, that you do not use new "SQL Server Native Client", but legacy "SQL Server", which returns new date/time datatypes as strings.
If you will use new ODBC driver then you will get mapped DATETIME2 to TDateTimeField. But precision of milliseconds will be limited to 3. So next question is if you need all 7 digits after decimal point ? Or you do not care ?
(if you do not care then probably you can use CAST in SQL statement to cast DATETIME2 to DATETIME)

[vertnik]

I do not care the precision, so I used CAST in SQL statement, and it worked.

Thank you all for the answers!