dataset filter by date not working

Posted on 2007-04-04
Last Modified: 2013-12-16
I have a dataview that I am trying to filter by date, and it is not working.
The table has a long date format "4/1/2007 5:51:13 PM"

                m_dv.RowFilter = "ImportDateTime > '" & C1DateEditRangeStart.Text & "' and ImportDateTime < '" & C1DateEditRangeStop.Text & "'"

here is the controls value from the watch window
-            C1DateEditRangeStop      {Text = "31-Dec-2006"}      C1.Win.C1Input.C1DateEdit
Question by:jackjohnson44
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
LVL 22

Expert Comment

ID: 18851257
Try converting the date to long format before calling the filter.

DateTime startDt = New DateTime(C1DateEditRangeStart.Text);
DateTime stopDt = New DateTime(C1DateEditRangeStop.Text);

 m_dv.RowFilter = "ImportDateTime > '" & startDt.ToLongDateString() & "' and ImportDateTime < '" & stopDt.ToLongDateString() & "'"
LVL 34

Expert Comment

ID: 18851475
Date values should be enclosed within pound signs (#). ... For example:


"Birthdate < #1/31/82#"

From DataColumn.Expression Property page in the Help Files, or here on-line

The same rules apply to expressions in RowFilters


Author Comment

ID: 18851506
that did not work, I am getting this error when I try to cast the date

Conversion from string "4/4/2007" to type 'Long' is not valid.
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

LVL 34

Expert Comment

ID: 18852089
What's not working?  None of the code suggested involves any conversion from string to long.


Author Comment

ID: 18852188
This breaks and says "Conversion from string "4/4/2007" to type 'Long' is not valid."
DateTime startDt = New DateTime(C1DateEditRangeStart.Text);
LVL 34

Expert Comment

ID: 18852649
I missed that ;-(.  But that's probably because I don't think that's what's needed, anyway.

Have you tried enclosing the dates in # in the filter statement?

m_dv.RowFilter = "ImportDateTime > #" & C1DateEditRangeStart.Text & "# and ImportDateTime < #" & C1DateEditRangeStop.Text & "#"


Author Comment

ID: 18853974
Thanks Roger,
That worked well.
One more quick question though.
The date in my db is long and has minutes and hours, so if I try to go between two dates, it is not picking up the end date

1/1/07 < 1/1/07 1:23 pm for example.

Is there a way to either treat the date in the db as short, or somehow compensate the dates from the picker?
LVL 34

Expert Comment

ID: 18854196
Something like

m_dv.RowFilter = "ImportDateTime > #" & C1DateEditRangeStart.Text & "# and ImportDateTime < #" & C1DateEditRangeStop.Text & " 11:59:59 PM #"



Author Comment

ID: 18857679
thanks, but I should have mentioned one more thing, I have a drop down that says equals, so I can't really append hours and minutes to the start date.  Is there a way to treat the db date as short in the filter?

.rowfilter ="shortdate(importdate) = " & c1dateeditrangestart.text
LVL 34

Expert Comment

ID: 18858105
1)  I don't understand what you mean by "I have a drop down that says equals, so I can't really append hours and minutes to the start date".  If, with your drop down that says equals, you could put

m_dv.RowFilter = "ImportDateTime > '" & C1DateEditRangeStart.Text & "' and ImportDateTime < '" & C1DateEditRangeStop.Text & "'"

which is what you had in your original post, why can you not put

m_dv.RowFilter = "ImportDateTime > #" & C1DateEditRangeStart.Text & "# and ImportDateTime < #" & C1DateEditRangeStop.Text & " 11:59:59 PM #"

That would not be changing the display value of any control, it would be purely internal to the rowfilter.

2)  I think it most unlikely that trying to change the format would make any difference.  The point here is that the Column's DataType is DateTime.  Formatting is purely to do with display: essentially it converts a DateTime value into a String (different datatype) in the specified format for display purposes.  Expressions work with values.  Sure, it would be possible, within the Expression, to covert the DataTime to a String before doing the comparison but then a STRING comparison between two short formatted dates would produce the "wrong" result.  For instance both 1 Jan 2007 and 30 Dec 2006 are LESS THAN 31 Dec 2006 so far as a STRING comprison is concerned.

3)  Although there are conversion functions that you can use in expressions they are, so far as I know, exhaustively described in the link that I posted earlier.  That make no reference to the sort of shortdate(importdate) conversion you describe.


Author Comment

ID: 18863683
I am sorry fot he confusion.  I have a drop down that says between, equals, before, after, then two datepickers.  Depending on the pulldown, if it says equals, the second drop don disappears, so you would select equals and only the first date picker.  

so if my options are
equals    1/1/07
I would like the following dates to show
1/1/07 5:01 am
1/1/07 .......

I would rather not add and subtract hours and minutes to my datepicker values, but I am not opposed to it.  I would think that there is a way to do something with the filter to get rid of this.

If I execute this sql command:
select CONVERT(char(10), ImportDateTime, 20)
It will give me just the date.  If I could use this in my filter, I would be very happy.

I would like to do what happens below, but get errors.

        Select Case ComboDateCriteria.Text
            Case "between"
                filterStr &= " AND CONVERT(char(10), ImportDateTime, 20) >= #" & C1DateEditRangeStart.Value & " and CONVERT(char(10), ImportDateTime, 20) <= #" & C1DateEditRangeStop.Value & "#"
            Case "equals"
                filterStr &= " AND CONVERT(char(10), ImportDateTime, 20) = #" & C1DateEditRangeStart.Value & "#"
            Case "before"
                filterStr &= " AND CONVERT(char(10), ImportDateTime, 20) < #" & C1DateEditRangeStart.Value & "#"
            Case "after"
                filterStr &= " AND CONVERT(char(10), ImportDateTime, 20) > #" & C1DateEditRangeStart.Value & "#"
        End Select
LVL 34

Accepted Solution

Sancler earned 500 total points
ID: 18865102
What will work in an SQL statement on the database will not necessarily work in an Expression in an Expression Column or in a RowFilter in a DataView.  Expressions have their own (more limited) capabilities.

If you want to use an Expression in a RowFilter for a DataView to filter by dates then you need to use something in the form

   NameOfFieldContainingDate - Operator - #DateAsString#

I assume that the 'NameOfFieldContainingDate' is ImportDateTime.  That contains a DateTime datatype.  That means that, whatever its display format, it has values in the hours, minutes and seconds slots.  That is true even if those values are 00:00:00.

The comparison operators available in Expressions do not include BETWEEN ... AND.  You are restricted to <, >, <=, >=, <>, =, IN and LIKE.  But you can also use Boolean operators AND and OR.  This means that you can, in effect, find what is BETWEEN x AND y by combining > (or >=) with < (or <=).  On these lines

   MyField > x AND MyField < y

I'll come back to operators (for our specific purpose) again in a moment.

The #DateAsString# requires a STRING value, representing a date or date and time (in a format that the system will recognise given its current culture), to be put between # and #.  A DateTimePicker's .Value property returns a DATETIME, not a STRING.  So the #DateAsString# part of any Expression will have to be based on "... #" & C1DateEditRangeStart.Text & "#" not on "... #" & C1DateEditRangeStart.Value & "#"

Now coming back to the operators for this specific purpose.  Remembering that the value which is on the left hand side of the equation is a DateTime - that is, including hours, minutes and seconds - if we just use the = operator and put a string representing just a date (without any time) on the right hand side of the equation the answer will be FALSE: using a value quoted in your first post

    4/1/2007 5:51:13 PM    does NOT equal     4/1/2007

does it?

But we know that ANY DateTime with a Date of 4/1/2007 is between 4/1/2007 0:0:01 AM and 4/1/2007 11:59:59 PM.  So, given that we want a record from the datatable to be included if the value in its ImportDateTime field falls anywhere in that range we will have to use the following Expression

   "ImportDateTime >= #" & C1DateEditRangeStart.Text & "0:0:01 AM# AND ImportDateTime <= #" & C1DateEditRangeStart.Text & "11:59:59 PM#".

For purposes of your combo, it may be treated as "Equals".  But in terms of the Expression syntax we have to find it by a (psuedo) BETWEEN ... AND approach: does this date WITH ITS TIME fall between the EARLIEST POSSIBLE TIME on this date and the LATEST POSSIBLE TIME on this date?

I've gone into pernickety detail because - counter-intuitively perhaps - "Equals" is the hardest one to get to grips with.  The others just work by analogy with that.


Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question