Author: Jason Madden 

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

Answer:

If you work with different (MS-)SQL-Server, you have sometimes the problem what the 
date value is in the correct format.
1   
2   function TForm1.GetSQLDateTimeFormat(UDL: string): string;
3   begin
4     Screen.Cursor := crSQLWait;
5     if ADOConnection1.Connected then
6       ADOConnection1.Close;
7     ADOConnection1.ConnectionString := 'FILE NAME=' + UDL;
8     ADOQuery1.SQL.Clear;
9     ADOQuery1.SQL.Add('sp_helplanguage @@LANGUAGE');
10    Application.ProcessMessages;
11    try
12      try
13        ADOQuery1.Open;
14      except
15        on E: Exception do
16          MessageBox(Handle,
17            PChar('Die Abfrage konnte nicht geöffnet werden:' + #13#10 + #13#10 +
18              E.message),
19            PChar('Fehler!'), 16);
20      end;
21      if (ADOQuery1.Active) and (ADOQuery1.RecordCount > 0) then
22        Result := ADOQuery1.FieldByName('dateformat').AsString;
23    finally
24      Screen.Cursor := crDefault;
25    end;
26  end;
27  
28  function DateTimeToSQLDateTimeString(Data: TDateTime; Format: string;
29    OnlyDate: Boolean = True): string;
30  var
31    y, m, d, h, mm, s, ms: Word;
32  begin
33    DecodeDate(Data, y, m, d);
34    DecodeTime(Data, h, mm, s, ms);
35    if Format = 'dmy' then
36      Result := IntToStr(d) + '-' + IntToStr(m) + '-' + IntToStr(y)
37    else if Format = 'ymd' then
38      Result := IntToStr(y) + '-' + IntToStr(m) + '-' + IntToStr(d)
39    else if Format = 'ydm' then
40      Result := IntToStr(y) + '-' + IntToStr(d) + '-' + IntToStr(m)
41    else if Format = 'myd' then
42      Result := IntToStr(m) + '-' + IntToStr(y) + '-' + IntToStr(d)
43    else if Format = 'dym' then
44      Result := IntToStr(d) + '-' + IntToStr(y) + '-' + IntToStr(m)
45    else
46      Result := IntToStr(m) + '-' + IntToStr(d) + '-' + IntToStr(y); //mdy: ; //US
47    if not OnlyDate then
48      Result := Result + ' ' + IntToStr(h) + ':' + IntToStr(mm) + ':' + IntToStr(s);
49  end;


Example:

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