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:
C# Code: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:
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.Code: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