Mirror

Adapting DateTime values for different SQL-Server formats (Views: 100)

Problem/Question/Abstract:

How can I adapt DateTime values for different SQL-Server formats?

Answer:

// Torry's Delphi Tips
// Author Sven Sohnel
// Listed 27.10.2002

{
Wenn man mit verschiedensprachigen (MS-)SQL-Servern arbeitet,
hat man ab und an das Problem, Datumswerte in ein für den
jeweiligen Server verständliches Format umzuwandeln.
}

{
If you work with different (MS-)SQL-Server, you have sometimes the
problem what the date value is in the correct format.
}

function TForm1.GetSQLDateTimeFormat(UDL: string): string;
begin
Screen.Cursor := crSQLWait;
if ADOConnection1.Connected then
ADOConnection1.Close;
ADOConnection1.ConnectionString := 'FILE NAME=' + UDL;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('sp_helplanguage @@LANGUAGE');
Application.ProcessMessages;
try
try
ADOQuery1.Open;
except
on E: Exception do
MessageBox(Handle,
PChar('Die Abfrage konnte nicht geöffnet werden:' + #13#10 + #13#10 +
E.Message),
PChar('Fehler!'), 16);
end;
if (ADOQuery1.Active) and (ADOQuery1.RecordCount > 0) then
Result := ADOQuery1.FieldByName('dateformat').AsString;
finally
Screen.Cursor := crDefault;
end;
end;

function DateTimeToSQLDateTimeString(Data: TDateTime; Format: string;
OnlyDate: Boolean = True): string;
var
y, m, d, h, mm, s, ms: Word;
begin
DecodeDate(Data, y, m, d);
DecodeTime(Data, h, mm, s, ms);
if Format = 'dmy' then
Result := IntToStr(d) + '-' + IntToStr(m) + '-' + IntToStr(y)
else if Format = 'ymd' then
Result := IntToStr(y) + '-' + IntToStr(m) + '-' + IntToStr(d)
else if Format = 'ydm' then
Result := IntToStr(y) + '-' + IntToStr(d) + '-' + IntToStr(m)
else if Format = 'myd' then
Result := IntToStr(m) + '-' + IntToStr(y) + '-' + IntToStr(d)
else if Format = 'dym' then
Result := IntToStr(d) + '-' + IntToStr(y) + '-' + IntToStr(m)
else
Result := IntToStr(m) + '-' + IntToStr(d) + '-' + IntToStr(y); //mdy: ; //US
if not OnlyDate then
Result := Result + ' ' + IntToStr(h) + ':' + IntToStr(mm) + ':' + IntToStr(s);
end;

//Example:
//Beispiel:

procedure ConvertSQLDateTime;
begin
ShowMessage(DateTimeToSQLDateTimeString(now, GetSQLLanguage('C:\DBEngl.udl')));
end;



<< Back to main page