MSSQL Date Functions

Question

When I add this to my where clause:

DueDate <= getDate() AND DueDate IS NOT null

I get a nice little failure that says: Could not create child: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.EvaluateException: The expression contains undefined function call getDate().

Any idea what I'm doing wrong?

---

Update Code in use:

private string getFilter(int mode) {
    String filter = "";
    if ((ALL_WORK_ORDERS & mode) == 0) {
        if ((mode & OUTSTANDING_WORK_ORDERS) == OUTSTANDING_WORK_ORDERS) {
            filter += "DueDate <= getDate() AND DueDate IS NOT null OR";
        }
        if ((mode & COMPLETED_WORK_ORDERS) == COMPLETED_WORK_ORDERS) {
            filter += " FinishedDate IS NOT  null";
        }
    }

    filter = filter.Trim();
    if (filter.EndsWith("OR")) {
        filter = filter.Remove(filter.Length - 2);
    }

    return filter;
}

And it is getting used here:

tblWorkOrderBindingSource.Filter = getFilter(mode);

In the table, DueDate is type datetime.

Side note: I can run

SELECT [ID]
      ,[WorkDesc]
      ,[DueDate]
  FROM [RentalEase].[dbo].[tblWorkOrder]
WHERE [DueDate] <= getDate() AND [DueDate] IS NOT null

in the MS SQL Server Management Studio Express without a problem.

---

Final Solution

    private string getFilter(int mode) {
        String filter = "";
        if ((ALL_WORK_ORDERS & mode) == 0) {
            if ((mode & OUTSTANDING_WORK_ORDERS) == OUTSTANDING_WORK_ORDERS) {
                filter += "DueDate <= #" + DateTime.Now.ToShortDateString() + "# AND DueDate IS NOT null  AND FinishedDate IS null OR";
            }
            if ((mode & COMPLETED_WORK_ORDERS) == COMPLETED_WORK_ORDERS) {
                filter += " FinishedDate IS NOT null";
            }
        }

        filter = filter.Trim();
        if (filter.EndsWith("OR")) {
            filter = filter.Remove(filter.Length - 2);
        }

        return filter;
    }

Answer 1

Is that supposed to be T-SQL? try this

DueDate <= getDate() 
AND  DueDate IS NOT null

Answer 2

If this is supposed to be sql then "||" is not the symbol for "OR" and "&&" is not the symbol for AND.

filter += "DueDate <= GetDate() AND DueDate is not NULL OR ";

Answer 3

System.Reflection.TargetInvocationException

This message did not come from Sql Server. Methinks you are actually writing C# in that string and getting it compiled/interpretted at runtime against .net types (instead of table defs).

Also, SQLServer 2005 has trouble with || and &&...

SELECT CASE WHEN 1=1 && 3=3 THEN 1 ELSE 0 END
--Incorrect syntax near '&'.

SELECT CASE WHEN 1=1 || 3=0 THEN 1 ELSE 0 END
--Incorrect syntax near '|'.

Answer 4

have you tried

filter += "DueDate <= #" + DateTime.Now + "# AND DueDate is not Null"

Answer 5

The exception is pretty clear on what is wrong here. You are specifying a method, getdate, which can't be used in a filter expression. See the documentation on the Expression property on the DataColumn class for what is valid and what is not:

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

That being said, instead of getdate, you need to concatenate the date into the filter (call the static Now property on the DateTime structure to get the current date). The link above will show you how to format dates properly (you can't just do it as indicated in Kelly's answer, although that answer is most of the way there).