Formatting DateTime string for Access Insert Into
lazarus / 16-Databases

----------------------------------------------------------------------
jsonnabend · 2015-11-22 21:46

I am trying to insert a row into a table use Jet SQL.  One of the
fields is date-time.  How do I format the data in the insert clause?

TIA.

----------------------------------------------------------------------
balazsszekely · 2015-11-23 05:37

Always use parameterized queries/scripts. Like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
SQLQuery1.Params.BeginUpdate; 
SQLQuery1.Params.ParamByName('TheDate1').AsDateTime := Now; 
SQLQuery1.Params.ParamValues['TheDate2'] := null; 
SQLQuery1.Params.EndUpdate;  SQLQuery1.ExecSQL;

----------------------------------------------------------------------
jsonnabend · 2015-11-23 16:55

I used an insert into command instead:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 'insert
into playhistory ([user], computer, trackid, [date]) values ('    +
QuotedStr(User) + ',' + QuotedStr(Host) + ',' + IntToStr(TrackID) +
',' + '{ts ' + ''''    + year + '-' + month + '-' + day + ' ' + hour +
':' + minute + ':' + second +  '''' + '}'  + ')'
That works fine.

----------------------------------------------------------------------
balazsszekely · 2015-11-23 17:06

It might work but is still wrong. That is why parameterized querys
where introduced in the first place.

----------------------------------------------------------------------
wp · 2015-11-23 21:56

I've never seen this "{ts ...}" syntax - but maybe I'm out-of-date...
AFAIK, Access has # characters around the date/time and the date in
year-month-day order, see
http://delphi.about.com/od/delphitips2007/qt/datetime_sql.htm

----------------------------------------------------------------------
Jkey · 2015-11-24 10:15

{ts ...} is an ODBC syntax, described here:
https://technet.microsoft.com/en-us/library/ms190234(v=sql.90).aspx
It's working if one uses for example TODBCconnection for connection to
an Access database.