Simple Query Problem Well!
-
I am doing a simple query on a date field in my table.
>= '2/1/2007' AND <= '2/5/2007'
Now when I run this I get everything from the 1st to the 4th,
The results do not include the 5th, If I change it to this
>= '2/1/2007' AND <= '2/6/2007' of Course I now get results for the 5th
How can I make the first query work because of course the 6th is not here yet?
Thank You
-
this is a common issue, because the field you are querying contains date AND time....so records exist that are 02/05/2007 10:49:00 AM for example, which is not less that 02/05/2007..it's slightly greater.
the easy way is to let the records be less than but NOT equall to 02/06/2007 for example: >= '2/1/2007' AND < '2/6/2007' so something that occurred just prior to midnite is included in your dataset.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The problem is I am using a Calendar java script to let people pick the date, is there a way to add one day to the date that they selected in the query?
-
select dateadd(mi,1439,'05/07/2007') returns 2007-05-07 23:59:00.000
select dateadd(s,86399,'05/07/2007') returns 2007-05-07 23:59:59.000,
so create a statement like :
WHERE DATEFIELD BETWEEN '2/1/2007' AND dateadd(mi,1439,'2/6/2007')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank You all for the quick response.
Below is my new query resulting from your ideas
SELECT *
FROM dbo.Monthly_Data
WHERE (date >= 'MMColParam') AND (date <(dateadd(s,86399, 'MMColParam2')))
And so far it seems to work
Thank You
-
You have a bug in the code you posted. If the time is after 23:59:59.000 on the last day, it will not return the data. You may think there is little chance of that, but why build a bug into it when it is easy not to?
It is almost always better to do date range querys in the form of
where MyDateCol >= @StartDateTime MyDateCol < @EndDateTime
So you would do your query like this:
declare @StartDate datetime declare @EndDate datetime
set @StartDate = '20070126' -- First day you want set @EndDate = '20070127' -- Last day you want
select * from dbo.Monthly_Data where date >= @StartDate and date < dateadd(day,1,@EndDate)
-
I simply use syntax like:
convert(varchar, @EndDate, 101) >= '2/6/2007'
This way your date with a time is converted only to a date and will always be equal or less than the same day.
-
Just my $.02...
Michael Valentine Jones was correct about the bug, and that's typically how i'd write my code.
As to Raymond, that would work, but depending on the data, size of table, indexes etc, it could run very, very slowly. When filtering it's always best to do the manipulation on value you're checking your data against. IE instead of converting the data from my table, convert(varchar, @EndDate, 101) manipulte on the other side @enddate > '2/7/2007'. That way you can still use your indexes properly and such.
-Luke.
-
You have a bug in that code.
convert(varchar, @EndDate, 101) will be converted to a string in the format '02/06/2007' and will be compared as a string to '2/6/2007'. That would mean that no date would ever be selected, since a date string in 101 format can only start with a 0 or a 1. Even if the string you were comparing it to was enter correctly as '02/06/2007', it would incorrectly select '02/07/2004', for example.
This is just one illustration of why the code I posted is a better way to do the query.
-
I do it the same way that Lowell and Michael do to maintain the ability to use indexes if one exists. The only difference is, I don't trust users even if they're fellow Developers... either in the WHERE or as an additional pair of SETs, I'll make bloody sure the time is stripped off...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '20070126' -- First day you want
SET @EndDate = '20070127' -- Last day you want
SELECT *
FROM dbo.Monthly_Data
WHERE DATE >= DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)
AND DATE < DATEADD(dd,DATEDIFF(dd,0,@StartDate),1)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply