Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Range condition in WHERE clause Expand / Collapse
Author
Message
Posted Monday, July 7, 2008 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 5:01 PM
Points: 33, Visits: 20
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.
Post #529603
Posted Monday, July 7, 2008 1:52 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, October 25, 2014 3:18 AM
Points: 3,108, Visits: 11,504
You are converting the dates to string before comparing them, instead of comparing dates. Do it like this:
where  EffDt >= '20080601' and EffDt < '20070101'

Post #529612
Posted Monday, July 7, 2008 2:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 5:01 PM
Points: 33, Visits: 20
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?
Post #529618
Posted Monday, July 7, 2008 2:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 23, 2008 1:43 PM
Points: 89, Visits: 206
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
Post #529625
Posted Monday, July 7, 2008 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 5:01 PM
Points: 33, Visits: 20
I tried this. This gives the same result as between.
It has rows from year 2007 and 2006...!!!
Post #529629
Posted Monday, July 7, 2008 2:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,220, Visits: 2,614
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'


Ken Simmons
http://twitter.com/KenSimmons
Post #529635
Posted Monday, July 7, 2008 2:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 28, 2011 5:01 PM
Points: 33, Visits: 20
thanks for the right syntax. It works.
Post #529638
Posted Monday, July 7, 2008 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,112, Visits: 15,487
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?
Post #529643
Posted Monday, July 7, 2008 3:26 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #529668
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse