Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

dataset filter by date not working

Posted on 2007-04-04
12
Medium Priority
?
1,719 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…

688 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