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?

Solution 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

Solution 2

Here's a function that should do the trick, thanks to feedback from David and Remy:

function AnsiDateStrToDate(AnsiDate : string) : TDate;
// Convert data in YYYY-MM-DD to TDate
var
  Fmt : TFormatSettings; // Does not need to be freed
begin
  Fmt := TFormatSettings.Create;
  Fmt.ShortDateFormat := 'YYYY-MM-DD';
  Fmt.DateSeparator := '-';
  result := StrToDate(AnsiDate,Fmt);
end;

Usage:

if AnsiDateStrToDate(myQuery.FieldByName('ActiveDate').AsString) = Date Then
  writeln('Today!')
Else
  writeln('Not Today!);

Comments

[David Heffernan]

If you must do this, use conversion functions that don't depend on global state. The overloads that accept a format settings parameter. But surely it's better to avoid converting to string at all.

[David Heffernan]

In other words the question is really about how to read a date field without using any string conversions. If possible.

[Chris Thornton]

@DavidHeffernan, Agreed. IMO, if you need it to be a date, use .AsDate instead of .AsString.

[David Heffernan]

So write that in the answer. Problem is, SQL date types aren't understood that way. Which is the essence of the question.

[Remy Lebeau]

The global formatting variables are not thread-safe, and have been completely removed in XE6. If you are going to use FormatSettings, the correct way is to use the overloaded version of StrToDate() that takes a TFormatSettings as input: Fmt := TFormatSettings.Create; Fmt.ShortDateFormat := 'YYYY-MM-DD'; Fmt.DateSeparator := '-'; ... := StrToDate(myQuery.FieldByName('ActiveDate').AsString, Fmt);

[Chris Thornton]

@RemyLebeau, good point about not being thread-safe. But DXE6 definitely does understand SysUtils.FormatSettings.ShortDateFormat. Anyway, I will take your advice on the overloaded StrToDate.

[mvanella]

Is .AsDate supposed to be available before XE2? I am running XE1 and it doesn't recognize it: 'TField' does not contain a member named 'AsDate' I couldn't find it in Embarcadero's documentation pre-XE2: docwiki.embarcadero.com/Libraries/XE2/en/Data.DB.TParam.AsDa‌​te

[David Heffernan]

@Chris Remy is giving the exact same advice I did. You absolutely need to fix the answer.

[Remy Lebeau]

I have fixed the answer.

[David Heffernan]

@mvanella Ah well, I guess I should not have deleted my answer if I'd known you wanted a text processing based solution. I don't understand why you don't want to get to the bottom of this. Does it not concern you that you've no idea where this date formatting comes from? What happens if on some other machine it comes back with a different format? Until you actually know where this text comes from, I cannot see how you can proceed.

[mvanella]

@davidheffernan Feel free to submit an answer and I will look it over. I never asked you to delete yours, that was all you. :)

[David Heffernan]

You already saw my answer. It wasn't very good. I think you really need to be trying to understand where this string comes from.

[mvanella]

@DavidHeffernan Switching locales a few times didn't seem to change the result, no errors were introduced. I am a little curious as to why that value is coming in weird, and I think Ken gave me some things to look into. But for now this question has gotten a little out of hand so I think I'd like to keep it simple. Also, you are an unpleasant individual with which to interact so I'd prefer to keep that interaction to a minimum. Regardless, thank you for your help.

[David Heffernan]

Why is it unpleasant? I'm giving you my advice. It's up to you what you do with it. Surely you came here for advice. I don't like to see problems closed when they have not been properly solved. I find that frustrating.

[David Heffernan]

@mvanella I'm happy now. Kobik's answer addresses the root problem quite nicely.