SQL Server And Visual Basic 6.0 Date Range
Posted on 2003-03-24
I am trying to do a search on a SQL Server table based on the date/time value. The column is defined as "smalldatetime" and I want to pull all the records that fall between the date range. I have two datepicker controls for the user to select the control and they are set for "vbshortDate". I know I am doing something stupid and I am overlooking something very easy. I know I have type mismatch problems and I have lot of different methods and have no luck so far.
Set MainBreakRs = MainBreakConn.Execute("Select WaterOffId, Convert(Varchar(20), TimeReceived, 0), " & _
"ValveDownId, StreetName, FromStreet, " & _
"ToStreet, Convert(Varchar(20), DateOff, 0), Convert(Varchar(20), EstDate,0), Convert(Varchar(20), DateOn, 0), CrewNo, Comments, " & _
"EditedBy, ModifiedBy, Convert(Varchar(20), ModifiedDate, 0) from WaterOff " & _
"where TimeReceived between '" & dtWateroffDateReceivedSearchFrom.Value & _
"' and '" & dtWaterToDateReceivedSearchtTo.Value & _
"' order by timereceived DESC ")
I have tried CONVERT(varchar(10), timereceived, 101) for the timereceived column and I have also tried CAST along with that to try to make it work. I have tried using FormatDateTime(dtWaterOffDateReceivedSearchFrom.value, vbshortDate/vbgeneraldate) and Fort(dtWaterOffDateReceivedSearchFrom.value, "MM/DD/YYYY") with no luck...
I need an extra set of eyes to take a look at this because I got tired. Thanks and any help is appreciated,