Solved

dataset filter by date not working

Posted on 2007-04-04
12
1,711 Views
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
0
Comment
Question by:jackjohnson44
  • 6
  • 5
12 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
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() & "'"
0
 
LVL 34

Expert Comment

by:Sancler
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

http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(vs.71).aspx

The same rules apply to expressions in RowFilters

Roger
0
 

Author Comment

by:jackjohnson44
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.
0
 
LVL 34

Expert Comment

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

Roger
0
 

Author Comment

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

Expert Comment

by:Sancler
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 & "#"

Roger
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:jackjohnson44
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?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18854196
Something like

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

perhaps?

Roger
0
 

Author Comment

by:jackjohnson44
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
0
 
LVL 34

Expert Comment

by:Sancler
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.

Roger
0
 

Author Comment

by:jackjohnson44
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
0
 
LVL 34

Accepted Solution

by:
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.

Roger
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
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.

747 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

14 Experts available now in Live!

Get 1:1 Help Now