Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Range condition in WHERE clause


Date Range condition in WHERE clause

Author
Message
Giya
Giya
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
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.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3264 Visits: 11771
You are converting the dates to string before comparing them, instead of comparing dates. Do it like this:
where  EffDt >= '20080601' and EffDt < '20070101'


Giya
Giya
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
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?
tbeadle
tbeadle
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
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
Giya
Giya
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 20
I tried this. This gives the same result as between.
It has rows from year 2007 and 2006...!!!
KenSimmons
KenSimmons
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 2614
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
Giya
Giya
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 20
thanks for the right syntax. It works.
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7664 Visits: 18105
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?
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4468 Visits: 9836
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search