We've got an Events table which has fields for StartDate and EndDate (date and time to be prceise). If the event is an all day event, it is stored with 12:00 AM in the entity object which saves it to the database with only the date value (no time value). All other events that don't start at midnight (12am) have the time value.

In code, we've got a filter set up get events that fall within a start or end range. If I query for events that start or end "BETWEEN '7/5/2005 12:00:00 AM' AND '7/5/2005 11:59:59 PM'", I get two rows when I only expected one row. The extra row that comes back has StartDate = 7/6/2005 and EndDate = 7/6/2005.

If I run the exact same query generated by LLBL (that I pulled from Visual Studio) in Query Analyzer, I end up with only the one row I was expecting.

Is it just me? Or is there some type of date rounding going on behind the scenes? I read a couple of threads about how the query you get from LLBL might not be the same as what is actually run (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1394) and saw another thread (which I've lost) about dates without times.

Any help is appreciated. This is one of those 'bang your head against your desk' problems right now.

Matt

mattsmith321 wrote:

... and saw another thread (which I've lost) about dates without times.

This is the other thread I lost: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3275 but it still doesn't address the problem I'm having.

This is the query (cleaned up and extra fields removed) that I get from Visual Studio and run in Query Analyzer: SELECT DISTINCT [Events].[StartDate] AS [StartDate], [Events].[EndDate] AS [EndDate], FROM ( [Events]
INNER JOIN [EventMembers] ON [Events].[EventID]=[EventMembers].[EventID]) WHERE ( [EventMembers].[MemberID] IN (6, 5, 7, 3, 1) And ( ( [Events].[StartDate] BETWEEN '7/5/2005 12:00:00 AM' AND '7/5/2005 11:59:59 PM') Or ( [Events].[EndDate] BETWEEN '7/5/2005 12:00:00 AM' AND '7/5/2005 11:59:59 PM') Or ( [Events].[StartDate] < '7/5/2005 12:00:00 AM' And [Events].[EndDate] > '7/5/2005 11:59:59 PM'))) ORDER BY [Events].[StartDate] ASC,[Events].[EndDate] DESC

Even though the dates are set to 7/5, when it runs through VS+LLBL it ends up with a row from 07/06/2005.

mattsmith321 wrote:

Even though the dates are set to 7/5, when it runs through VS+LLBL it ends up with a row from 07/06/2005.

And if I change the StartDate and EndDate in the database to 07/06/2005 12:00:01 AM, I get the results I want (record is not returned for the 7/5 query).

Yes, I am talking to myself on this forum. How come nobody else works at 10pm on a Saturday night?

From SQL Server Books Online:

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

We have logic in our code to subtract a millisecond from the EndDate part of the range. We added the logic since the input from the form would come through as 7/5/2005 1:00:00 PM to 7/5/2005 2:00:00 PM but we didn't want it to actually pull back events that started at 2:00:00 PM. I thought we would be safe by subtracting a millisecond from the value. Guess I'll switch the login to subtract one second from the EndDate.

Thanks for everyone's help!

The only difference in the queries is that when it's ran from LLBLGen Pro, it uses parameters, and when it is ran from query analyzer it uses strings which are converted to time/date.

Though it is still strange the 7/6 date is appearing in the resultset... I don't do anything with the values, I just pass them on (don't blame the messenger! )