Converting from SQL Date to DateTime in Delphi XE

Question

I've searched google and SO with no usable fixes for this. I am retrieving a date from a table in SQL Server. I need to convert this to a DateTime object in Delphi. The problem seems to lie in the value coming in from SQL. The version of SQL Server on which I am testing this is SQL Server 2012 (not R2).

If I display the value retrieved from a query as a string I get:

myQuery.FieldByName('ActiveDate').AsString;

I get the value

2014-06-15

myQuery is a TADOQuery type.

DateTime doesn't seem to like this format since all of these fail:

myQuery.FieldByName('ActiveDate').AsDateTime;
StrToDateTime(myQuery.FieldByName('ActiveDate').AsString);
StrToDate(myQuery.FieldByName('ActiveDate').AsString);

The failure message from the caught exception that gets logged is:

''2014-06-15'' is not a valid date and time

Is there a solution to this that doesn't involve a complicated string manipulation and that would not be affected by the locale of the server?

Answer 1

I could reproduce this behavior (SQL Server 2008 R2).
SQL-Server data type date (2008+) maps to TWideStringField (ftWideString) with ADO provider SQLOLEDB.1.

Accessing this field (TWideStringField) with .AsDateTime will raise an exception as described in the quesion: '2014-06-15' is not a valid date and time.

Here is a related (or duplicate?) question: Delphi 6, ADO, MS database “Date” field is same as ftWideString

Possible solutions:

• use the SQL-Server native client e.g. Provider=SQLNCLI10.1; -> date is mapped to TDateField
• SELECT via CAST(ActiveDate AS datetime) and then .AsDateTime works as expected.
• use datetime data type on SQL-Server instead of date
• access the date field via solution provided by @Chris Thornton