Invalid argument in a DateDiff() function

TheJudeDude

I am getting an error when I run the following query:

"SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, DateDiff('d',2009-11-08,[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')"

The error is "Invalid argument to Function. Function name is DateDiff" The 'd' is a standard argument, The date is standard, and so is the data field [TICKET-NEXT-DUE-DATE]. Here is how I have it in the code:

SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, " +
"DateDiff('d'," + strDate + ",[TICKET-NEXT-DUE-DATE]) AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')

This statement I took straight out of MS Access. Any help would be appreciated!

Jude

Luc Pattyn

within SQL statements literal dates need delimiters, either ' or # or [] depending on DB. :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

TheJudeDude

Thanx Luc! That brought me to another error of Invalid Function Name. Function name is DATEDIFF??? Isn't datediff() a standard function? As I said before, the same string works in Access.... Here's what the string looks like now: "SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, DATEDIFF('d','2009-11-08','2009-11-08') AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')" Thanx!

Jude

The Man from U N C L E

TheJudeDude wrote:

DATEDIFF('d','2009-11-08','2009-11-08')

You now have to many quotes. It should be one of the following:

DATEDIFF(d,'2009-11-08','2009-11-08')

Or

DATEDIFF(dd,'2009-11-08','2009-11-08')

Or

DATEDIFF(day,'2009-11-08','2009-11-08')

Of course this is a poor example as datediff of the same dates is always 0.

If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk

TheJudeDude · DATEDIFF(d,'2009-11-08','2009-11-08')

True, bad example..I was just trying to troubleshoot, so I just pasted in the same date. I saw the bad quotes and changed that: "SELECT TICKET-STORE, TICKET-ACCT-MANAGER, TICKET-TYPE, TICKET-CONTRACT-DATE, TICKET-NEXT-DUE-DATE, TICKET-LAST-PAID-DATE, DATEDIFF(d,'2009-11-08','2009-11-08') AS Expr1, TICKET-TICKET-NBR FROM TICKET WHERE (TICKET-TYPE ='O')" But I am still getting an error stating:Build Error : Invalid Function Name, Function name is 'DATEDIFF'. ??

Jude

Luc Pattyn

DateDiff is an SQL function, how can a C#, C++, or VB compiler complain about it? FWIW: If your SQL statement spans more than a single line of source code, you should take appropriate measures, which depend on your programming language. Please show actual code and error message. And check the line numbers as to where the error occurs. :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

TheJudeDude

I shortened up the SQL a bit, but I am getting the same error The error is on DbReader = DbCommand.ExecuteReader():

	OdbcConnection DbConnection = new OdbcConnection("DSN=rsss");
		OdbcCommand DbCommand = DbConnection.CreateCommand();
		DbCommand.CommandText = "SELECT TICKET-STORE, DATEDIFF(day,'" + strDate + "','" + strDate + "') AS EXP FROM TICKET";
		DbConnection.Open();
		try
		{
			DbReader = DbCommand.ExecuteReader();
			while(DbReader.Read())
			{
				intStore = Int32.Parse(DbReader\["TICKET-STORE"\].ToString());
			}
		}

		catch(OdbcException caught)
		{
			Console.WriteLine(caught.ToString());
			Console.Read();
			
			DbConnection.Close();
		}

Here is the error:

System.Data.Odbc.OdbcException: ERROR [HY000] Build Error: Invalid Function Name. Function name is 'DATEDIFF'.
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteReader()
at acctmgr.Class1.Main(String[] args) in c:\documents and settings\brian\my documents\visual studio projects\acctmgr\class1.cs:line 43

Thanx for your help!

Jude

Luc Pattyn

I don't see anything wrong, but then I'm not a DB expert at all. However, from earlier messages, I think you target SQL Server 2000, and this[^] seems to suggest you use SqlConnection instead of OdbcConnection. Which would mean a different connection string, some C# code changes, and probably some SQL changes too. :)

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

TheJudeDude

I figured it out. The contacted the company who produced the connector. It does not support a DateDiff() function :-/ I also contacted the third party vender and they stated that they have a built in function for it. Hopefully that works. Thanks a lot for your time and help!

Jude