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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
egit plugin on eclipse 8 41
Angular JS Route 3 54
Where to download and how to install sqldmo.dll 5 36
Not seen Link button 5 19
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

862 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now