470,593 Members | 2,374 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Using DateAdd with ODBC drivers

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


Expand|Select|Wrap|Line Numbers
  1. [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
  2. SELECT DATEADD ("dd", -2, '#1969-12-31 20:00:00#') AS NextCalibration FROM TblCalibItems;
Expand|Select|Wrap|Line Numbers
  1. [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
  2. SELECT DATEADD ("second", -2, '2022-02-02') AS NextCalibration FROM TblCalibItems;
Apr 14 '22 #1
5 6921
352 Expert Mod 256MB
Perhaps I've got this wrong but it appears that you are writing this code in Access
If so, try

Expand|Select|Wrap|Line Numbers
  1. SELECT DATEADD ("d", -2, "#1969-12-31 20:00:00#") AS NextCalibration FROM TblCalibItems;
Expand|Select|Wrap|Line Numbers
  1. SELECT DATEADD ("s", -2, "#2022-02-02#") AS NextCalibration FROM TblCalibItems;
Apr 14 '22 #2
5,439 Expert Mod 4TB
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 :)
Apr 14 '22 #3
352 Expert Mod 256MB
Thanks @zmbd
I approved the OP's post but for some reason was unable to get my own reply to appear!
Apr 15 '22 #4
199 Expert 128KB
DATEADD is not field of your table but a function, so try:

Expand|Select|Wrap|Line Numbers
  1. SELECT DateAdd("d", -2, "#1969-12-31 20:00:00#") AS NextCalibration FROM TblCalibItems;
Also, the correct arguments for DateAdd are required: DateAdd function.
Apr 15 '22 #5
32,298 Expert Mod 16PB
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.
Apr 15 '22 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by hugh welford | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.