vb.net date compare

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 & "'"

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.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 35

YZlatCommented: 2015-07-31

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 & "#"

LVL 37

Miguel OzSoftware EngineerCommented: 2015-07-31

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() & "'"

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?

LVL 40

Jacques Bourgeois (James Burger)PresidentCommented: 2015-07-31

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

vb.net date compare

Who is Participating?