Databases
StackOverflow2013
Postsdbo
POEF generated SQL Query returns wrong result
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEF generated SQL Query returns wrong result
    text
    copied!<p>I want to filter on a specific date. Because of the control I use I can also filter on a range of dates, but when I filter on a single date it should also work so I have total flexibility.</p> <p>When I filter on 2004-11-24 my code generates the following query with Entity Framework. However somehow the query returns me the results of 2004-11-25 instead of 2004-11-24.</p> <p>What is wrong with this query? </p> <pre><code>SELECT TOP (26) [Filter1].[Id] AS [Id], [Filter1].[AliquotId] AS [AliquotId], [Filter1].[ScientificStudyId] AS [ScientificStudyId], [Filter1].[ExpectedSpecimenId] AS [ExpectedSpecimenId], [Filter1].[ExpectedAliquotId] AS [ExpectedAliquotId], [Filter1].[VisitId] AS [VisitId], [Filter1].[BoxId] AS [BoxId], [Filter1].[ParticipantId] AS [ParticipantId], [Filter1].[Product] AS [Product], [Filter1].[BodyMaterial] AS [BodyMaterial], [Filter1].[AliquotNr] AS [AliquotNr], [Filter1].[ReservationName] AS [ReservationName], [Filter1].[Visit] AS [Visit], [Filter1].[Win32Color] AS [Win32Color], [Filter1].[Position] AS [Position], [Filter1].[Barcode] AS [Barcode], [Filter1].[Temperature] AS [Temperature], [Filter1].[DivergentStorageTemperature] AS [DivergentStorageTemperature], [Filter1].[BoxName] AS [BoxName], [Filter1].[CollectionDate] AS [CollectionDate], [Filter1].[Positioned] AS [Positioned], [Filter1].[CheckedOut] AS [CheckedOut], [Filter1].[CheckinDate] AS [CheckinDate], [Filter1].[CheckoutDate] AS [CheckoutDate], [Filter1].[LabelsPrinted] AS [LabelsPrinted], [Filter1].[ParticipantNr] AS [ParticipantNr], [Filter1].[Specimen] AS [Specimen], [Filter1].[ContainerType] AS [ContainerType], [Filter1].[ContainerTypeId] AS [ContainerTypeId], [Filter1].[Quantity] AS [Quantity], [Filter1].[Unit] AS [Unit], [Filter1].[QuantityUnit] AS [QuantityUnit], [Filter1].[StudyVersion] AS [StudyVersion], [Filter1].[ThawCount] AS [ThawCount], [Filter1].[Sop] AS [Sop], [Filter1].[NrOfRemarks] AS [NrOfRemarks], [Filter1].[RemarkSummary] AS [RemarkSummary] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[AliquotId] AS [AliquotId], [Extent1]. [ScientificStudyId] AS [ScientificStudyId], [Extent1].[ExpectedSpecimenId] AS [ExpectedSpecimenId], [Extent1].[ExpectedAliquotId] AS [ExpectedAliquotId], [Extent1].[VisitId] AS [VisitId], [Extent1].[BoxId] AS [BoxId], [Extent1].[ParticipantId] AS [ParticipantId], [Extent1].[Product] AS [Product], [Extent1].[BodyMaterial] AS [BodyMaterial], [Extent1].[AliquotNr] AS [AliquotNr], [Extent1].[ReservationName] AS [ReservationName], [Extent1].[Visit] AS [Visit], [Extent1].[Win32Color] AS [Win32Color], [Extent1].[Position] AS [Position], [Extent1].[Barcode] AS [Barcode], [Extent1].[Temperature] AS [Temperature], [Extent1].[DivergentStorageTemperature] AS [DivergentStorageTemperature], [Extent1].[BoxName] AS [BoxName], [Extent1].[CollectionDate] AS [CollectionDate], [Extent1].[Positioned] AS [Positioned], [Extent1].[CheckedOut] AS [CheckedOut], [Extent1].[CheckinDate] AS [CheckinDate], [Extent1].[CheckoutDate] AS [CheckoutDate], [Extent1].[LabelsPrinted] AS [LabelsPrinted], [Extent1].[ParticipantNr] AS [ParticipantNr], [Extent1].[Specimen] AS [Specimen], [Extent1].[ContainerType] AS [ContainerType], [Extent1].[ContainerTypeId] AS [ContainerTypeId], [Extent1].[Quantity] AS [Quantity], [Extent1].[Unit] AS [Unit], [Extent1].[QuantityUnit] AS [QuantityUnit], [Extent1].[StudyVersion] AS [StudyVersion], [Extent1].[ThawCount] AS [ThawCount], [Extent1].[Sop] AS [Sop], [Extent1].[NrOfRemarks] AS [NrOfRemarks], [Extent1].[RemarkSummary] AS [RemarkSummary], row_number() OVER (ORDER BY [Extent1].[CollectionDate] ASC) AS [row_number] FROM [dbo].[ClaimedAliquotPositionsReadModels] AS [Extent1] WHERE (cast('0ebebae6-594d-4be0-ac8b-a078013f1370' as uniqueidentifier) = [Extent1].[ScientificStudyId]) AND ([Extent1].[CheckoutDate] &gt;= convert(datetime2, '2004-11-24 00:00:00.0000000', 121)) AND ([Extent1].[CheckoutDate] &lt; convert(datetime2, '2004-11-25 00:00:00.0000000', 121)) ) AS [Filter1] WHERE [Filter1].[row_number] &gt; 0 ORDER BY [Filter1].[CollectionDate] ASC </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload