I am getting a list of information using a TypedListDAO and putting it in a grid.

It is only displaying the date in the grid but it must be retrieving the date and time as the auto filter shows many instances of the same date. (It shows unique values)

Is there any way to get it to only retrieve the date portion of a datetime field?

Are you sure the autoFilter shows only unique values? Are the datetime values returning differents datetime values (like 2007-01-01 00.00.000 and 2007-01-01 10.15.200)?

Anyway, I think you can make a typedList field that mix some dateParts (using DBFunctionCalls) and make your own date. However that will not return a Datetime type.

Yes, the autoFilter shows unique values.

Yes, the DataTable bound to the grid holds different DateTime values e.g. (From the actual data) 31/10/2007 08:57:53 and 31/10/2007 12:24:40

Currently the grid (Janus GridEx) does the date formatting but it filters on the data in the datatable, not on the values it displays.

As I said, I'm doing this with a TypedListDAO, with the fields defined as such:

fields = New ResultsetFields(10)
'...
fields.DefineField(DeliveryNoteFields.DateAdded, 5)
'...

I know I can do certain aggregate functions such as SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.CountDistinct

You say you think you can make a typedList field that mix some dateParts. Are you talking about before the generated code is done? Any ideas if and how it could be done in the context of a TypedListDAO, generating the list on the fly?

You can add an extra column to the TypedList to return DATEPART(myDate) values. Using DBFunctionCall to get the DATEPART().

Or you can use a DynamicList from the begining.

Would you please post the SQL query that you want to execute? And post the one that's already executed?

Thanks.

I don't think I'm quite following your first suggestion. Could you give

I am using a dynamic list

A simplified example is: SELECT BookingRef, CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, DateAdded))) AS DateAdded FROM BookingDeliveryNote

Oh, I thought you used a TypedList.

Anyway, try this out:

ResultsetFields fields = new ResultsetFields( 2 );
fields.DefineField( BookingDeliveryNoteFields.BookingRef, 0);
fields.DefineField( BookingDeliveryNoteFields.DateAdded, 1, "DateAdded" );

fields[1].ExpressionToApply = 
new DbFunctionCall("CONVERT", new object[] { "DATETIME", 
new DbFunctionCall("FLOOR", new object[] { 
new DbFunctionCall("CONVERT", new object[] { "FLOAT", BookingDeliveryNoteFields.DateAdded}) })});

It doesn't work. The error reads: Line 1: Incorrect syntax near '@LO866c64011'. Incorrect syntax near the keyword 'AS'.

The full generated query is:

SELECT [bkngdb].[dbo].[BookingDeliveryNote].[BookerName], [bkngdb].[dbo].[BookingDeliveryNote].[Cancelled], [bkngdb].[dbo].[BookingDeliveryNote].[ClientName], [bkngdb].[dbo].[BookingDeliveryNote].[ContactEmail], [bkngdb].[dbo].[BookingDeliveryNote].[ContactName], CONVERT(@LO866c64011, FLOOR(CONVERT(@LO4bed2e2b2, [bkngdb].[dbo].[BookingDeliveryNote].[DateAdded]))) AS [Date Added], [bkngdb].[dbo].[BookingDeliveryNote].[DeliveryMethod], [bkngdb].[dbo].[BookingDeliveryNote].[Destination], [bkngdb].[dbo].[BookingDeliveryNote].[BookingRef], [bkngdb].[dbo].[BookingDeliveryNote].[HoldReason], [bkngdb].[dbo].[BookingDeliveryNote].[HoldTicketIssue], [bkngdb].[dbo].[BookingDeliveryNote].[InitialAgent], [bkngdb].[dbo].[BookingDeliveryNote].[LeadPassengerName], [bkngdb].[dbo].[BookingDeliveryNote].[Origin], [bkngdb].[dbo].[BookingDeliveryNote].[Printed], [bkngdb].[dbo].[BookingDeliveryNote].[Ticket], CONVERT(@LO866c64013, FLOOR(CONVERT(@LO4bed2e2b4, [bkngdb].[dbo].[BookingDeliveryNote].[TravelDate]))) AS [Travel Date], (SELECT TOP 1
 COUNT(DISTINCT [bkngdb].[dbo].[BookingRefund].[BookingRef]) AS [BookingRef] FROM [bkngdb].[dbo].[BookingRefund]  WHERE ( [bkngdb].[dbo].[BookingDeliveryNote].[BookingRef] = [bkngdb].[dbo].[BookingRefund].[BookingRef])) AS [Refunded], [bkngdb].[dbo].[BookingDeliveryNote].[DeliveryAddress], [bkngdb].[dbo].[BookingDeliveryNote].[NonIssueRequested], [bkngdb].[dbo].[BookingDeliveryNote].[NonIssued] FROM [bkngdb].[dbo].[BookingDeliveryNote]  WHERE ( ( [bkngdb].[dbo].[BookingDeliveryNote].[Printed] = @Printed5 AND [bkngdb].[dbo].[BookingDeliveryNote].[Cancelled] = @Cancelled6 AND [bkngdb].[dbo].[BookingDeliveryNote].[Branch] = @Branch7 AND [bkngdb].[dbo].[BookingDeliveryNote].[LeadPassengerName] = @LeadPassengerName8)) ORDER BY [bkngdb].[dbo].[BookingDeliveryNote].[Ticket] ASC
    Parameter: @LO866c64011 : String. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: "DATETIME".
    Parameter: @LO4bed2e2b2 : String. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "FLOAT".
    Parameter: @LO866c64013 : String. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: "DATETIME".
    Parameter: @LO4bed2e2b4 : String. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "FLOAT".
    Parameter: @Printed5 : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: 0.
    Parameter: @Cancelled6 : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: 0.
    Parameter: @Branch7 : AnsiString. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "001".
    Parameter: @LeadPassengerName8 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "".

I tried with and without a field alias and got the same error.

I had to adapt the code as I use VB and I gave you a simplified example. The actual code here was:

fields("Travel Date").ExpressionToApply = New DbFunctionCall("CONVERT", New Object() {"DATETIME", New DbFunctionCall("FLOOR", New Object() {New DbFunctionCall("CONVERT", New Object() {"FLOAT", EvolviDeliveryNoteFields.TravelDate})})})

which LLBLGen Pro Runtime Library version are you using?

It looks like it's v2.0.50727. I'm downloading 2.5 now!

Try it with v.2.5

I've upgraded to the latest version of LLBLGen Pro, removed the references, regenerated my code, referenced the new dlls and I still get the same error.

VS is reporting the Runtime Version of SD.LLBLGen.Pro.ORMSupportClasses.NET20 and .DQE.SqlServer.NET20 as v2.0.50727 and the Version as 2.5.0.0

Sorry, here is the correct code (Tested):

fields[1].ExpressionToApply =
new DbFunctionCall("CONVERT(DATETIME,{0})", new object[] {
new DbFunctionCall("FLOOR", new object[] { 
new DbFunctionCall("CONVERT(FLOAT, {0})", new object[] {BookingDeliveryNoteFields.DateAdded}) })});

Top man!

Thanks Walaa.