[SOLVED] SQL datetime2(7)
lazarus / 16-Databases

----------------------------------------------------------------------
vertnik · 2017-07-10 12:22

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 · 2017-07-10 12:49

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 · 2017-07-10 12:54

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 · 2017-07-10 19:07

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 · 2017-07-10 21:30

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);var  S: String;  Dt: TDateTime; 
Format: TFormatSettings;begin  S := '2017-06-01 10:57:48.3814412'; //
this doesn't work  S := '2017-06-01 10:57:48.381'; // this works  
Format.DateSeparator := '-';  Format.ShortDateFormat := 'yyyy-mm-dd'; 
Format.TimeSeparator := ':';  Format.LongTimeFormat := 'hh:nn:ss.z'; 
Format.DecimalSeparator := '.';  Dt := StrToDateTime(S, Format);  
ShowMessage(DateTimeToStr(Dt, Format)); end;
I think you need a new datatype like TDateTime2 (like it's in MSSQL)
with higher msec precision.

----------------------------------------------------------------------
howardpc · 2017-07-10 22:05

Quote from: rvk on July 10, 2017, 11:30:46 pmI'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 · 2017-07-12 05:25

Quote from: vertnik on July 10, 2017, 09:07:16 pm 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 · 2017-07-19 10:56

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

Thank you all for the answers!