July 7, 2008 at 1:36 pm
Hi,
i'm checking for a date range in SQL query from .NET app. My WHERE clause has this condition.
WHERE CONVERT(CHAR(10), EffDt, 101) between '06/01/2008' and '06/30/2008'
This returns records in June for all years, like 2007 and 2006 too. Any idea why?
Convert funtion takes only the yy in year? like '06/01/20' and '06/30/20'?
101 - this is supposed to take yyyy and not just yy.
any help will be appreciated. thanks.
July 7, 2008 at 1:53 pm
You are converting the dates to string before comparing them, instead of comparing dates. Do it like this:
where EffDt >= '20080601' and EffDt < '20070101'
July 7, 2008 at 2:01 pm
The user is choosing the date range from calendar control and it displays in mm/dd/yyyy format as 06/01/2008. Is there any way around?
July 7, 2008 at 2:11 pm
Hello,
Something like this should work
where convert(varchar(10),EffDt,101) >= '06/01/2020'
and convert(varchar(10),EffDt,101) <= '06/30/2020'
I confirm, the 101 is the Convert format for MM/DD/YYYY.
However, consider these results:
select count(*)
from statement
where convert(varchar(10), stmnt_creation_date, 101)
between '12/01/2006' and '12/31/2006'
-----------
306
Note that even though the date range was for 2006 it pulled in some 2003 records. Weird.
select count(*)
from statement
where stmnt_creation_date >= convert(datetime, '12/01/2006 00:00:00')
and stmnt_creation_date <= convert(datetime, '12/31/2006 23:59:59')
-----------
282
Note that by using specific time values it got the right answer of 282 records.
select convert(varchar(10), stmnt_creation_date, 101)
from statement
where convert(varchar(10), stmnt_creation_date, 101)
between '12/01/2006' and '12/31/2006'
and datepart(year,stmnt_creation_date) = 2006
order by stmnt_creation_date asc
It also got the right answer when the datepart function limited the return to 2006 data.
So there's some thing in the conversion process that's not quite right.
I'd switch from the between logic to the date with time limits.
Regards,
Terry
July 7, 2008 at 2:17 pm
I tried this. This gives the same result as between.
It has rows from year 2007 and 2006...!!!
July 7, 2008 at 2:29 pm
I am not sure why Mickael's solution will not work. If it is a datetime value the comparison should work. To get your original query to work you need to convert it back to a datetime.
WHERE Cast(CONVERT(CHAR(10), EffDt, 101) as datetime) between '06/01/2008' and '06/30/2008'
July 7, 2008 at 2:34 pm
thanks for the right syntax. It works.
July 7, 2008 at 2:41 pm
Giya (7/7/2008)
The user is choosing the date range from calendar control and it displays in mm/dd/yyyy format as 06/01/2008. Is there any way around?
If you pass this stuff back as dates and not as character fields, you wouldn't be running into string comparisons. I'm not sure why noone wants to do this, but even if you have to pass your date params as strings, convert them back to dates before using.
as in :
....
where effdate >=cast(@startdate as datetime) and effdate<cast(@enddate as datetime)
....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 7, 2008 at 3:26 pm
Matt Miller (7/7/2008)
Giya (7/7/2008)
The user is choosing the date range from calendar control and it displays in mm/dd/yyyy format as 06/01/2008. Is there any way around?If you pass this stuff back as dates and not as character fields, you wouldn't be running into string comparisons. I'm not sure why noone wants to do this, but even if you have to pass your date params as strings, convert them back to dates before using.
as in :
....
where effdate >=cast(@startdate as datetime) and effdate<cast(@enddate as datetime)
....
And of course, if you do it this way and there is an index on effdate the index could be used. If you use the previously posted methods - the index cannot be used.
If you are worried about the time, then just strip the time - as in:
where effdate >= dateadd(day, datediff(day, 0, @startdate), 0)
and effdate < dateadd(day, 1, dateadd(day, datediff(day, 0, @enddate), 0)
If the column effdate is a datetime column - @startdate and @enddate in the above will be implicitly converted to a datetime data type to match the column.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply