Querying database using the same date does not return data

Question

Hi all. I have a little problem with a query. I'm selecting data using the between command, But when I select the initial date equal to the final date the query doesn't show data.

I know that the problem is the format. In my database I got something like this:2009-05-22 15:32:52.000. But when I send the date parameter from ASP.NET Page I sent only the Date (2009-05-22).

So, I want to fix this thing. I cannot change the Datetime inside the database.

I was thinking adding 1 day to the final date, So when the user select the same date I change the range behind the scene and then show the data. What do you think?

What would you do?

Answer 1

Hi,
try modifying the end date parameter before passing int to the select. So if end date is 2009-05-22 make it 2009-05-22 23:59:59.00 I know it is not best solution to hardcode such things but it is easy to be done

Answer 2

2009-05-22 will translate as 2009-05-22 00:00:00 so setting the start and end the same will only select records that occur at exactly midnight. Adding either 1 day (midnight to midnight) will normally be good enough if you don't have data points occurring too frequently. If there is a lot of data then you best setting the time part of the end time to 23:59:59.999

Answer 3

When I need to get all results for a day I create a DateTime with no time component (0:00) and then add a day and subtract a second to get a DateTime with a time component of 23:59:59. The DateTime class in .NET makes this pretty easy.

                DateTime dtStart = new DateTime( 2009, 5, 22 );
            DateTime dtEnd = dtStart.AddDays( 1 ).AddSeconds( -1 );

Answer 4

When you send a date like 2009-05-22, sql server interprets it as 2009-05-22 00:00:00.000. This means that when you use that date with a between query where "the initial date equal to the final date", you're checking on a range of exactly one millisecond.

Instead, you need to set the 2nd between value to the end of the day, so that your between expression covers a full one day range. Or, my preferred option is to use two expressions like this:

WHERE ([datetimecolumn] >= '2009-05-22' AND [datetimecolumn] < '2009-05-23')

Notice the difference in the comparison operators; the end of the range is not inclusive.

Answer 5

You have 2 options... either secretly do a date range by adding one day the search date entered and then doing a date range or running your select where you compare only a portion of the date in the database to the data given to search for using DATEPART.

http://msdn.microsoft.com/en-us/library/ms174420.aspx

http://www.w3schools.com/Sql/func_datepart.asp

if you are going to do a data range, then do it

WHERE date >= 'entered date' AND data < 'entered date + 1'

instead of trying to catch the very last second of the day so you can do a <= on the end part of the day

Answer 6

If the query that the users are using is strictly dates (i.e., the user does not have the option of entering the time), then I would definitely create a date-only column in the database that matches the date-time column (but strips the time, of course). This would best be done with a function, since you don't want to have to maintain a second copy of the same data. Then query on that column.

I would recommend not doing any one-second-to-midnight ranges, since the granularity of the data type is less than one second, and such ranges are, therefore, incorrect.

Alternatively, the original proposal of stripping the time and adding one full day to the range should work fine as long as you change it from a BETWEEN to a "table.column <= @date AND @date < table.column + 1" (making it an exclusive interval on the top end).

Answer 7

If you have an input of say @matchDate... you can create a start/end date for your range. You should use the @startDate as the 0 time point for your date, with @endDate being that value +1. You will want to match >= @startDate, and < @endDate.

@matchDate DATETIME -- passed in with a given datetime
...
DECLARE @startDate DATETIME;
DECLARE @endDate DATETIME;

-- this will give you the DateTime without the Time part, note if you 
-- are storing dates in UTC, you will want to pass in your starting 
-- DateTime as the UTC zero hour, and skip this conversion.
SET @startDate = DATEADD(day, 0, DATEDIFF(day, 0, @created_date))

-- this will give you the @startDate + 1 Day
Set @endDate = DATEADD(dd, 1, @startDate);

...
SELECT ... 
FROM ... 
WHERE [MyDateCol] >= @startDate AND [MyDateCol] < @endDate

When you do a given date to match against, it's best to specify a range, you COULD do the reduction of both your match date and your column date to the date part, but this will be less performant than using a >= and < for a range.

Again, when storing in UTC, you will want to have your start-end in UTC. Which I recommend for apps that will serve multiple timezones. I recently posted an article for doing paged results in a sproc, that includes a date-time matching if you are interested.

Answer 8

a couple of answers given would really help, i have been using this method, if it intrests you,

    Select * from table1 Year(DtaeColumn)=Year(@OurDate) and Month(DtaeColumn)=Month
(@OurDate)  and Day(DtaeColumn)=Day(@OurDate)

this leaves no scope for anything ele....