Searching for data between two dates.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Searching for data between two dates.

Author

Topic

Pauley1968
Starting Member

9 Posts

Posted - 2009-01-21 : 10:39:06

I'm using ASP and SQL to allow users to search for data between two dates. Users select a beginning date and an ending date from dropdowns that I have formatted as mm/dd/yyyy. The field in my table is a varchar field also formatted as mm/dd/yyyy. As long as I search for 2 dates within the same month the report returns the data with no problem. As soon as I use 2 dates in different months the report returns no data at all. Im not sure why it does that, any ideas? Is it because im dealing with a varchar field in my table?

Here is how my select looks right now. The firstdate and seconddate variables are the 2 dates selected from the dropdowns

strSQL = "SELECT location, Count(1) as 'NumberOfLogins' FROM table WHERE DayLoggedIn >= '"&firstdate&"' AND DayLoggedIn <= '"&seconddate&"' GROUP BY location Order by 2 desc;"

Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:40:37

yup. because it considers dates as varchar values and not as date values.thats the reason why you should always try to use proper datatype for fields. using datetime type fields should have made matters much easier. now you've two options
1. change columns to dataetime
2. use cast or convert to cast them to datetime in where before doing the comparison

Pauley1968
Starting Member

9 Posts

Posted - 2009-01-21 : 11:47:39

Oh ok, Thanks man! I tried to do a conversion type of select that I've used in the past in other context with a hard coded date but I don't seem to be able to get the syntax right for the firstdate and seconddate variables. Its looks like this:

The way I used it before:
SELECT location, SUM(Const) FROM table
WHERE convert(datetime,DayLoggedIn,101) > convert(datetime,'9/1/2008',101) and convert(datetime,DayLoggedIn,101) < convert(datetime,'10/1/2008',101)
GROUP BY location;

What I've tried for this app:
strSQL = "SELECT location, Count(1) as 'NumberOfLogins' FROM table WHERE convert(datetime,DayLoggedIn,101) >= '"&convert(datetime,'firstdate',101)&"' AND convert(datetime,DayLoggedIn,101) <= '"&convert(datetime,'seconddate',101)&"' AND location LIKE '%" & locale & "%' GROUP BY location Order by 2 desc;"

Probably way off but im a bit of a noob.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 12:35:27

[code]
SELECT location, SUM(Const) FROM table
WHERE convert(datetime,DayLoggedIn,101) BETWEEN '2008-09-01' AND '2008-10-01'
GROUP BY location
[/code]

Pauley1968
Starting Member

9 Posts

Posted - 2009-01-21 : 15:03:10

Hello, thanks for the help. I took your example and made it into this.

strSQL = "SELECT location, Count(1) as 'NumberOfLogins' FROM table WHERE convert(datetime,DayLoggedIn,101) BETWEEN '"&firstdate&"' AND '"&seconddate&"' AND location LIKE '%" & locale & "%' GROUP BY location Order by 2 desc;"

Once I finally got the syntax figured out it works the same way. Only before it just wouldnt return any data if the dates spanned over 2 months, now it gives a 500 error. Am I still missing something?

I most recently tried this:

strSQL = "SELECT location, Count(1) as 'NumberOfLogins' FROM table WHERE convert(datetime,DayLoggedIn,101) BETWEEN convert(datetime,'"&firstdate&"',101) AND convert(datetime,'"&seconddate&"',101) AND location LIKE '%" & locale & "%' GROUP BY location Order by 2 desc;"

Does the same thing. Any ideas?

GnR_Slash
Starting Member

14 Posts

Posted - 2010-02-24 : 09:09:13

quote:

Originally posted by visakh16


SELECT location, SUM(Const) FROM table
WHERE convert(datetime,DayLoggedIn,101) BETWEEN '2008-09-01' AND '2008-10-01'
GROUP BY location

hi visakh16!

I have a doubt about dates in SQL Server
data between 'MM/dd/yyyy' and 'MM/dd/yyyy'
or
data between 'yyyy/MM/dd' and 'yyyy/MM/dd'

in Microsoft Access i do like this:
data between #MM/dd/yyyy# and #MM/dd/yyyy#

i need a code that works in brazil, japan, EUA, ....

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 10:24:52

quote:
Originally posted by Pauley1968

Hello, thanks for the help. I took your example and made it into this.

strSQL = "SELECT location, Count(1) as 'NumberOfLogins' FROM table WHERE convert(datetime,DayLoggedIn,101) BETWEEN '"&firstdate&"' AND '"&seconddate&"' AND location LIKE '%" & locale & "%' GROUP BY location Order by 2 desc;"

Once I finally got the syntax figured out it works the same way. Only before it just wouldnt return any data if the dates spanned over 2 months, now it gives a 500 error. Am I still missing something?

I most recently tried this:

strSQL = "SELECT location, Count(1) as 'NumberOfLogins' FROM table WHERE convert(datetime,DayLoggedIn,101) BETWEEN convert(datetime,'"&firstdate&"',101) AND convert(datetime,'"&seconddate&"',101) AND location LIKE '%'" & locale & "'%' GROUP BY location Order by 2 desc;"

Does the same thing. Any ideas?

i think you missed couple of '. try using modified query above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources