[DanielDucharme]

I wasn't sure where I should put this so I apologize if this is the wrong place. I am doing some work on my companies website and am currently trying to add the ability to search through records by Sale Date. I have it almost working except for a few strange dates that are getting though. The webpage is using ASP.NET in C# and here is the code for the WHERE clause of the query:

if (LowSaleDate != "-1")
{
	string[] date = LowSaleDate.Split('/', '-');
	string Month = date[0];
	string Day = date[1];
	string Year = date[2];
 
	if(concat == true)
	{
		sql = sql + @" AND ";
	}
	else
	{
		concat = true;
	}
 
	sql = sql + @"(Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) > '" + Year +
		@"' OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) > '" + Month + 
		@"' AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) >= Len('" + Month +
		@"') AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '" + Year +
		@"') OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) = '" + Month +
		@"' AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) = Len('" + Month +
		@"') AND Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1) >= '" + Day +
		@"' AND LEN(Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1)) >= Len('" + Day +
		@"') AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '" + Year + @"')) ";
}
if (HighSaleDate != "-1")
{
	string[] date = HighSaleDate.Split('/', '-');
	string Month = date[0];
	string Day = date[1];
	string Year = date[2];
 
	if(concat == true)
	{
		sql = sql + @" AND ";
	}
	else
	{
		concat = true;
	}
 
	sql = sql + @"(Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) < '" + Year +
		@"' OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) < '" + Month +
		@"' AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) <= Len('" + Month +
		@"') AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '" + Year +
		@"') OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) = '" + Month + 
		@"' AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) = Len('" + Month +
		@"') AND Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1) <= '" + Day + 
		@"' AND LEN(Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1)) <= Len('" + Day +
		@"') AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '" + Year + @"')) ";
}

Now here is the problem when I search for all sales with a LowSaleDate = 12/13/2009 and a HighSaleDate = 1/11/2010 I get the following results:

Map/Lot	Location 		SaleDate 	SalePrice 	Style 		Neighborhood	Square Footage 
04-083 	119 ALFRED DROWN ROAD	12/16/2009 	$311,000 	Bungalow  	0040 			1,528 SF 
08-005 	46 BLUFF ROAD		12/21/2009 	$680,000 	Antique 	0040 			3,410 SF 
20-168 	130 UPLAND WAY		12/7/2009 	$590,000 	Colonial 	0080 			3,088 SF 
22-040 	151 CHURCH STREET	12/2/2009 	$250,000 	Cape 		0090 			1,248 SF

As you can see there are 2 rows that do not belong there and it is also missing 1 row dated 1/8/2010 which should be showing up. The dates are the only part of the where clause in my testing. Let me know if you need any more information.

[wcrivelini]

daniel

I'm not truly familiar to .Net, but it seems to me you've made so many string manipulation when you could use a simple WHERE clause in your SQL statement with something like:

WHERE SalesDate BETWEEN @LowSaleDate AND @HighSaleDate

I guess the first thing to do is to show the resulting SQL statement.

Another important thing: dates are a nightmare, because each RDBMS vendor implements different syntaxes to deal with dates. I wrote an article a couple of years ago about the different date formats used by the most common RDBMS vendors and one of the most common date formats used is YYYYMMDD. You might try that too.

[DanielDucharme]

I made all of those string manipulations because the dates are stored in the database as text and on the webpage they are entered as strings. This was done to eliminate the problem where every part was interpreting the date differently and not communicating it correctly. Thus here is the finished query when it is run:

SELECT ID, MapLot, SaleDate, SalePrice, Location, STYLE, NBC, SqFt
FROM [Sale]
WHERE 
	(Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) > '2009' 
		OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) > '12' 
			AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) >= Len('12') 
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2009') 
		OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) = '12' 
			AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) = Len('12') 
			AND Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1) >= '13'
			AND LEN(Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1)) >= Len('13')
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2009'))
	AND (Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) < '2010' 
		OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) < '1' 
			AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) <= Len('1') 
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2010') 
		OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) = '1' 
			AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) = Len('1') 
			AND Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1) <= '11'
			AND LEN(Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1)) <= Len('11')
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2010'))

EDIT: I discovered that the problem is with the LowSaleDate as when I just run it with that I am getting those 2 sales below the date that I showed in my first post, so here is the query with just LowSaleDate:

SELECT ID, MapLot, SaleDate, SalePrice, Location, STYLE, NBC, SqFt
FROM [Sale]
WHERE 
	(Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) > '2009' 
		OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) > '12' 
			AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) >= Len('12') 
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2009') 
		OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) = '12' 
			AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) = Len('12') 
			AND Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1) >= '13'
			AND LEN(Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1)) >= Len('13')
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2009'))

EDIT 2: After working on it some I found one problem with the LEN and have edited it, Month now correctly works but for some reason day is still not working.

SELECT ID, MapLot, SaleDate, SalePrice, Location, STYLE, NBC, SqFt
FROM [Sale]
WHERE 
	(Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) > '2009'
		OR ((Mid(SaleDate, 1, Instr(SaleDate, '/')-1) > '12'
				AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) = LEN('12')
				OR LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) > LEN('12'))
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2009')
		OR (Mid(SaleDate, 1, Instr(SaleDate, '/')-1) = '12' 
			AND LEN(Mid(SaleDate, 1, Instr(SaleDate, '/')-1)) = LEN('12')
			AND (Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1) >= '11' 
				AND LEN(Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1)) = LEN('11')
				OR LEN(Mid(SaleDate, Instr(SaleDate, '/')+1, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')-1)) > LEN('11'))
			AND Mid(SaleDate, Instr(Instr(SaleDate, '/')+1, SaleDate, '/')+1, 4) = '2009'));