Recent

Author Topic: Formatting DateTime string for Access Insert Into  (Read 2185 times)

jsonnabend

  • New Member
  • *
  • Posts: 15
Formatting DateTime string for Access Insert Into
« on: November 22, 2015, 10:46:23 pm »
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.

GetMem

  • Hero Member
  • *****
  • Posts: 3752
Re: Formatting DateTime string for Access Insert Into
« Reply #1 on: November 23, 2015, 06:37:01 am »
Always use parameterized queries/scripts. Like this:
Code: Pascal  [Select][+][-]
  1.   SQLQuery1.Params.BeginUpdate;
  2.   SQLQuery1.Params.ParamByName('TheDate1').AsDateTime := Now;
  3.   SQLQuery1.Params.ParamValues['TheDate2'] := null;
  4.   SQLQuery1.Params.EndUpdate;
  5.   SQLQuery1.ExecSQL;

jsonnabend

  • New Member
  • *
  • Posts: 15
Re: Formatting DateTime string for Access Insert Into
« Reply #2 on: November 23, 2015, 05:55:26 pm »
I used an insert into command instead:
Code: Pascal  [Select][+][-]
  1. 'insert into playhistory ([user], computer, trackid, [date]) values ('
  2.     + QuotedStr(User) + ',' + QuotedStr(Host) + ',' + IntToStr(TrackID) + ',' + '{ts ' + ''''
  3.     + year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + second +  '''' + '}'  + ')'

That works fine.

GetMem

  • Hero Member
  • *****
  • Posts: 3752
Re: Formatting DateTime string for Access Insert Into
« Reply #3 on: November 23, 2015, 06:06:17 pm »
It might work but is still wrong. That is why parameterized querys where introduced in the first place.

wp

  • Hero Member
  • *****
  • Posts: 7356
Re: Formatting DateTime string for Access Insert Into
« Reply #4 on: November 23, 2015, 10:56:25 pm »
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
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Jkey

  • New Member
  • *
  • Posts: 44
Re: Formatting DateTime string for Access Insert Into
« Reply #5 on: November 24, 2015, 11:15:22 am »
{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.

 

TinyPortal © 2005-2018