Using DateAdd with ODBC drivers

[JXWD]

Hi I have tried all kinds of formats and syntax I just can't get DateAdd to work with ODBC drivers...

System Windows NT PICWEB01 6.3 build 9600 (Windows Server 2012 R2 Standard Edition) i586
ODBC library Win32
ODBCVER 0x0350

EG

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SELECT DATEADD ("dd", -2, '#1969-12-31 20:00:00#') AS NextCalibration FROM TblCalibItems;

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SELECT DATEADD ("second", -2, '2022-02-02') AS NextCalibration FROM TblCalibItems;

etc...

[isladogs]

Perhaps I've got this wrong but it appears that you are writing this code in Access
If so, try

SELECT DATEADD ("d", -2, "#1969-12-31 20:00:00#") AS NextCalibration FROM TblCalibItems;

and

SELECT DATEADD ("s", -2, "#2022-02-02#") AS NextCalibration FROM TblCalibItems;

[zmbd]

What is your backend?
What is your frontend?
The syntax can be quite specific to what FE and BE you are using... as seen here starting around post6:

home > topics > asp / active server pages > questions > dateadd in sql statement not working (Post#6)

[Edit] Did not see isladogs post to your question until after I had made mine due to it being in moderation status.; however, it follows along the same line as the thread I linked you to... syntax is everything :)

[isladogs]

Thanks @zmbd
I approved the OP's post but for some reason was unable to get my own reply to appear!

[cactusdata]

DATEADD is not field of your table but a function, so try:

SELECT DateAdd("d", -2, "#1969-12-31 20:00:00#") AS NextCalibration FROM TblCalibItems;

Also, the correct arguments for DateAdd are required: DateAdd function.

[NeoPa]

I would point you towards a few issues with your tests. Some have previously been identified / hinted at already.

Jet/ACE SQL allows both the appropriate SQL format quotes (') as well as the double-quotes ("). Mixing them, as you have, is probably also perfectly acceptable - even if it does look confused.

Acceptable values for the Interval parameter are listed in the article Cactus linked you to. Neither of "dd" nor "second" is in that list.

Some versions of SQL (T-SQL being an example) don't allow for date literal values and require a properly formatted date string (In such cases they require proper SQL quotes (') as delimiters). Access supports the same DateTime literals in SQL as it does in VBA. These require DateTime (Hash or Octothorp #) indicators and do not require string quotes, of any variety, around them. For more on what is acceptable for DateTimes see Literal DateTimes and Their Delimiters (#). You'll see there that your date formatting is otherwise optimal. Probably the best of the available standard SQL formats for them. Certainly the one with the least possibility of ambiguity.

I think, between us, we've explained every problem illustrated in your SQL code. I would just add, before I stop, my congratulations for formatting your question such that all the relevant information was available to us at the start.