Delphi & ADO: datetime to string conversion

Question

Hi,

I use Delphi 2006 and ADO to connect to a MS Access database. Some of the fields I retrieve are Date fields (in Access formatted as "Medium Date" i.e. 20-Apr-2010) however I have to retrieve them as Strings:

FValue:=FAccessADOQuery.Fields.FieldByName(FIELD_NAME).AsString;

and then the fields are formatted as follows: 4/20/2010.

My question is: when does this formatting take place and how can I customize it? Is it ADO settings (could not find anything there) or the OS (I use Win XP ENG with US locale)? Or maybe it's Delphi?

Thanks! Lou

Answer 1

Ok, just found it. It's delphi general settings (if missing then the values are taken from the OS):

DateSeparator   := '-';
ShortDateFormat := 'dd-mmm-yyyy';

And now the returned value is "20-Apr-2010".

Answer 2

You can retreive the Value as DateTime and use this function to convert it to your format

FValue:=FAccessADOQuery.Fields.FieldByName(FIELD_NAME).AsDateTime;

function DateToMediumDate(const Date: TDate): string;
var
  y, m, d: Word;
begin
  DecodeDate(Date, y, m , d);

  Result := Format('%d-%s-%d', [d, ShortMonthNames[m], y]);
end;

Answer 3

the ShortDateFormat and LongTimeFormat variables are used to format an TDateTimeField to string.

you can change the value of theses variables or try something different like this :

Dt :TDateTime;
Ds :String;
begin
//FAccessADOQuery.Fields.FieldByName(FIELD_NAME).AsString
Dt:=FAccessADOQuery.Fields.FieldByName(FIELD_NAME).AsDateTime;
Ds:=FormatDateTime('dd-mmm-yyyy',dt);
end;