Hi

I need to strip the time off a column in a SQL Server table that is of the type datetime and then do a comparison. I have tried using the DBFunctionCall with Convert get errors. The error occurs on the DBFunction call. The code that I am using is:

        ResultsetFields fields = new ResultsetFields(4);
        fields.DefineField(StaffFields.Name, 0);
        fields.DefineField(StatusFields.Descr, 1,"Status");
        fields.DefineField(NotificationFields.NotificationDt, 2);
        fields.DefineField(NotificationFields.WeekDate, 3);

        // add joins to tables
        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add(NotificationEntity.Relations.StatusEntityUsingStatusId, JoinHint.Inner);
        bucket.Relations.Add(NotificationEntity.Relations.StaffEntityUsingStaffId, JoinHint.Inner);

        // add where clauses
        bucket.PredicateExpression.Add(StatusFields.AccessLvlId == staff.AccessLevelCode);
        if (staff.AccessLevelCode == 2)
            bucket.PredicateExpression.AddWithAnd(StaffFields.WorkGroupId == staff.WorkGroupId);
        else
            bucket.PredicateExpression.AddWithAnd(StaffFields.WorkGroupId == wrkGrpID);

        // CONVERT(VARCHAR,WeekDate ,103)
        EntityField2 nvlDateCeck = NotificationFields.WeekDate;
        nvlDateCeck.SetExpression(new DbFunctionCall("CONVERT",new object[]{"char(10)",NotificationFields.WeekDate,"103"}));

        bucket.PredicateExpression.AddWithAnd(nvlDateCeck == dtWeekEnd.ToShortDateString());

        //sort by name
        SortExpression sort = new SortExpression();
        sort.Add(StaffFields.Name | SortOperator.Ascending);                    

        //set up the table to store the result
        DataTable dtResults = new DataTable();

        adapter.FetchTypedList(fields, dtResults, bucket, 0, sort, false);

Do I have the correct syntax for the DbFunctionCall.

Sorry. PRoblem solved. I was missing quotes around function call. thanks

That DBFuncionCall involves constants (VARCHAR is a constant in this case). So you have to do this this way:

 // CONVERT(VARCHAR,WeekDate ,103)
EntityField2 nvlDateCeck = NotificationFields.WeekDate;
     nvlDateCeck.SetExpression(new DbFunctionCall("CONVERT(VARCHAR, {0}, {1})",new object[]{NotificationFields.WeekDate,"103"}));