vb.net date compare

FCapo
FCapo used Ask the Experts™
on
Hi,

I'm trying to filter my dataset using two dates but I keep getting the error:
Cannot perform '>=' operation on System.DateTime and System.String.

The code is :
hoursBindingsource1.Filter = "Nom='" & My.Settings.empName & "' and date >= '" & startOfWeek & "' and date <='" & endOfWeek & "'"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try converting your startOfWeek and endOfWeek strings to Datetime

also if you are using Access database, use # instead of single quotes, but I am assuming you are using SQL Server

And why not use BETWEEN:

hoursBindingsource1.Filter = "Nom='" & My.Settings.empName & "' and date BETWEEN '" & startOfWeek & "' and '" & endOfWeek & "'"

Open in new window


or if it is Access database, use

hoursBindingsource1.Filter = "Nom='" & My.Settings.empName & "' and date BETWEEN #" & startOfWeek & "# and #" & endOfWeek & "#"

Open in new window

Miguel OzSenior Software Engineer
Top Expert 2009
Commented:
Assuming startOfWeek and endOfWeek are DateTime types, they need to be converted to string as shown below:
hoursBindingsource1.Filter = "Nom='" & My.Settings.empName & "' and date >= '" & startOfWeek.ToString() & "' and date <='" & endOfWeek.ToString() & "'" 

Open in new window

If above code does not work, please answer the following questions:
q1. Is the date column a string type or date type?
q2. How is the date column storing the dates, which format? (e.g. YYYY-mm-dd, etc.)
q3. What are startOfWeek and endOfWeek types?
Top Expert 2015
Commented:
This is an all too common error: somewhere in your system, you are mixing oranges and apples, dates and strings.

A string that shows a date is not a date, it is a string. You cannot go around string for the users, because they usually type a date in a TextBox, and a TextBox returns a String.

One way around that is to use some type of calendar control, but they usually takes up too much space or are not very convivial for the user. So most of the times, you are stuck with the TextBox.

But you should never handle the date as a string in your code. The first thing you should do is to take the date from the TextBox, validate it and then store it in a Date variable, never directly in a String.

Once a date is in a Date variable, it is a date, and you can handle it easily everywhere a date is needed, without all the fuss such as the one you encounter. It should be a date in the code, it should also be a date in the database. Too many programmers have problems with dates because they record them in a text field in the database.

You should never treat a date as a string, because the same string means different things for different computers, or for the computer and the database. "03/02" can be March 2nd for one, and "February 3rd" for the other.

Except for the interface, always treat dates as Date variables and Date fields in your code.

The same things holds for numbers by the way.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial