DATEDIFF() function does not work

[leifoet]

I get an error for sql test codes (1)-(2)-(3).

...
sql = "SELECT *, DATEDIFF(day, date(), ActDate) AS V_datediff FROM Activity" (1)
sql = "SELECT *, DATEDIFF(day, '20/02/2020', '20/05/2020') AS V_dateDiff FROM Activity" (2)
sql = "SELECT *, DATEDIFF(day, '2020/02/20', '2020/05/20') AS V_DateDiff FROM Activity" (3)
sql = "SELECT * FROM Activity ORDER BY Actdate ASC" (=no error)
rs.Open sql, Conn

The same error message for (1)-(2)-(3)
Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Thanks for tips.

 

[bborissov]

DATE() i snot built-in SQL Server function.
For the others use so called ISO date standard: YYYYMMDD

SELECT DATEDIFF(day, '20200202', '20200520')

Borislav Borissov
VFP9 SP2, SQL Server

 

[Andrzejek]

(1) Date() – try GETDATE() instead
[tt]
sql = "SELECT *, DATEDIFF(day, [blue]GETDATE()[/blue], ActDate) AS V_datediff FROM Activity"
[/tt]
(2) and (3) – you are using strings in DATEDIFF function. You need to convert these strings to dates.



---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

The intended DATEDIFF calculation (from the database) is still faltering.

sql = "SELECT *, DATEDIFF(day, getdate(), ActDate) AS V_datediff FROM Activity"

error
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'getdate' in expression

Dates converted and tested on 'date'.
SQL also gives errors

response.write(isdate(formatdatetime(20/02/2020,2)))&" hello <BR>"
response.write(isdate(cdate("20/02/2020")))&" hello2 <BR>"
response.write(isdate(cdate(#20/02/2020#)))&" hello3 <BR>"

Answers:
True hello
True hello2
True hello3

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DATEDIFF(day, cdate('20200220'), cdate('20200520') AS V_DateDiff from Activity'.
voor

sql = "Select *, DATEDIFF(day, cdate('20200220'), cdate('20200520') AS V_DateDiff from Activity"

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DATEDIFF(day, cdate('20/02/2020'), cdate('20/05/2020') AS V_DateDiff from Activity'.
voor

sql = "Select *, DATEDIFF(day, cdate('20/02/2020'), cdate('20/05/2020') AS V_DateDiff from Activity"

Can I still test something?
Thanks for possible alternative tools to connect the DATEDIFF function to the database.

 

[bborissov]

Then this is not SQL Server.
I just saw that you query ACCESS.
So please ask the question in appropriate forum. This is for SQL Server not for Access

Borislav Borissov
VFP9 SP2, SQL Server

 

[leifoet]

Found a working code: replaced day with 'd' in DATEDIFF
Thanks to bborissov and Andrzejek