Solved

dataset filter by date not working

Posted on 2007-04-04
12
1,717 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
[X]
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
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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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
 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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 and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

710 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