I am using LLBL GEn generated code in DataAdapter template. I have a table FormLog which contains one field SubmissionDate of type Date Type.

Now from FormLog I want to fetch all the records of specific date.

Suppose the table contains data as

ID SubmissionDate 1 01/Feb/2008 12:45:36 AM 2 01/Feb/2008 12:26:00 AM 3 01/Feb/2008 12:00:00 AM

Now I wat all the records with SubmissionDate = 01/Feb/2008, irespective of time.

I I use normal predicate expression it does not work as it compares time also.

 DateTime submissionDate =  new DateTime(2008, 2, 1);

 IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.FormLogEntity);
 IRelationPredicateBucket bucket = new RelationPredicateBucket();
 bucket.PredicateExpression.Add(FormLogFields.SubmissionDate == submissionDate);

 //now fetch the entity collection

Above code returns only record number 3. But I want all the 3 records

It can be achieved with sql as

SELECT ID, SubmissionDate FROM FormLog WHERE (CONVERT(VARCHAR(10), SubmissionDate, 7) = 'Feb 20, 08')

How to achieve this in LLBLGEN Generated code?

My second question is I want to fetch collection of all the distinct SubmissionDate irespective of time.

That is something like

SELECT DISTINCT CONVERT(VARCHAR(10), SubmissionDate, 7) AS [Mon DD, YY] FROM FormLog

How to achieve this in LLBLGEN Generated code?

bucket.PredicateExpression.Add(FormLogFields.SubmissionDate == submissionDate);

bucket.PredicateExpression.Add(FormLogFields.SubmissionDate >= submissionDate.Date);
bucket.PredicateExpression.AddWithAnd(FormLogFields.SubmissionDate < submissionDate.Date.AddDays(1));

Would this work?

It can be achieved with sql as

SELECT ID, SubmissionDate FROM FormLog WHERE (CONVERT(VARCHAR(10), SubmissionDate, 7) = 'Feb 20, 08')

How to achieve this in LLBLGEN Generated code?

Convert is a database function, so you should use DBFunctionCall() Please check the LLBLGen Pro manual's section "Using the generated code -> Calling a database function" Espesially the Using a function call in a predicate part. And the Specifying constants for function parameters part.

My second question is I want to fetch collection of all the distinct SubmissionDate irespective of time.

That is something like

SELECT DISTINCT CONVERT(VARCHAR(10), SubmissionDate, 7) AS [Mon DD, YY] FROM FormLog

How to achieve this in LLBLGEN Generated code?

You should use a DynamicList that has one field, and again you'll need to use a DBFunctionCall() on that field.