[RESOLVED] How to use rowfilter to filter year, or month+year

[droopy928gt]

Hello All,

It seems that for every answer I find another question, if I'm lucky only 1, is raised. Also this one seemed so simple, but has already kept me busy for over 2 days with only finding dead ends.

I'm trying to filter a dataview for e.g dates with a year larger then 2005, or e.g items with a month+year larger then Oct 2005. The compared to value comes from a datatimepicker which depending upon users input has a custom format of either full date, month+year or year as its textproperty.

I know that rowfilter is very limited in its options, and that's where my problem lies.

The dates in my dataview are real dates, M/dd/yyyy, so no conversion needed. The date columns type is also DateTime as such. The problem though is that I'm not able to separate the year, or month+year from the date columns value to compare against the datetimpickers value.

On several places on the internet I found references to using the format function.

.rowfilter = "FORMAT(columnname, 'yyyy') > '2005'"

But unless I'm doing something wrong, the format function is not supported under rowfilter according MSDN.

On this forum I found a comment stating that it's simply done using > or >=, etc. But that works only with full dates, not when only stating a year or month+year. as such when doing something like that on August 5th, you'd get all results for August 6th and onwards.

I also found references in combination with String.Format. But again the only formatting done is for the values compared to, not for the comparing value from the date column.

-How do you filter e.g items with a year larger then 2005, if at all possible?
-How do you filter e.g items with a month + year larger then Oct 2005, if at all possible?

Thanks for any input on my question.

Best regards,

Leon

[MonkOFox]

Are you using the datatable object to update the actual database table? If not,
you could always just modify the actual query you're using to retrieve the data initially.

... where datepart('year',thedate) > 2005

You could also use datatable.select(whereClause) which returns an array of datarow objects I believe.

I'm not familiar with dataview.rowfilter

Justin

[yo mismo]

where date > 2005 is the same than where date>12/31/2005

Where month+year is more tricky because the leap years.
Where date > Oct 2005 ---> where date >10/31/2005

Just add a a day to february when (year mod 4=0) and (not(year mod 100=0) or(year mod 400=0))

[droopy928gt]

Both MonkOFox and yo mismo, thank you for the replies.

@MonkOFox
I specifically, if possible, like to use rowfilter. The reason is that I already have the datatable in memory. This filtering is meant in case the user, after retreiving the data, wants to filter on a certain date range. Using rowfilter I'm trying to prevent having to contact the MySQL server again, while I basically already have the information in memory. I can also easily undo the filtering by just setting the rowfilter string to "", which is a bonus for me.

From above you can already conclude that I'm not using the datatable to update the database.

@yo mismo
I'm using the option date from and date to using 2 datetimepickers. As such I was trying to avoid what you're suggesting here, because it means a lot more code as I need to start looking on when to use e.g. 1-1-year or 31-12-year. In case of month+year that would be even more code as I need again to look at when to use 1-month-year or worse 28/29/30/31-month+year. Latter 28/29 for February and 30/31 for the other months.



It would be so much cleaner if I could somehow format the date coming from the datatable. The code would be cleaner.

I can do that with the dates that I'm comparing the date from the datatable to using following techniques.

Both examples come out of some of the posts from jmcilhinney, thanks for that.  
Very interesting coding as I never knew this was possible.

.RowFilter = String.Format("DateFrom >= #{0:M/dd/yyyy}# AND DateTo <= #{0:M/dd/yyyy}#", Date.Now.AddDays(-1)) 

.RowFilter = String.Format("DateFrom >= #{0}# AND DateTo<= #{1}#", Me.pickerFrom.Text, Me.pickerTo.Text)

I'm however not able to manipulate the DateFrom or DateTo parts.

I'm slowly but surely becoming convinced though that the difficult way is the only way due to the limited possibilities when using rowfilter.

If anyone knows of a better way though??????? I'd greatly appreciate a kick in the right direction.

Thanks and best regards,

Leon

[MonkOFox]

Here are some different conversions for datetime objects in sql server:

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' + 
CONVERT(CHAR(19),GETDATE())  
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' + 
CONVERT(CHAR(8),GETDATE(),10)  
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' + 
CONVERT(CHAR(10),GETDATE(),110) 
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' + 
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' + 
CONVERT(CHAR(9),GETDATE(),6) 
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' + 
CONVERT(CHAR(24),GETDATE(),113)
Here is the output from the above script:

1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb  5 2003  5:54AM
2) HERE IS MM-DD-YY FORMAT ==>02-05-03
3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003
4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003
5) HERE IS DD MON YY FORMAT ==>05 Feb 03
6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567

Justin

[droopy928gt]

Hi Justin,

Thanks, but as written previous. If at all possible I'd like to use rowfilter as to not have to contact the MySQL server again.

I've now abandonded the idea of manipulating the date from the data column in the datataview using rowfilter as I do not want to waste anymore time since I do not seem to be able to find the answer. As such I was pondering upon manipulating the compared to string as that is possible.

I came up with below code which for me is relatively easy and clean and always gives me a full date independant from the customformat of the datetimepicker.

It uses 3 radiobuttons to set the custom format for the 2 datetimpickers to 
either "dd MMM yyyy", or "MMM yyyy" or "yyyy".

'Define strings containing the datefrom and dateto values
Dim DateFrom As String
Dim DateTo As String
'Check which of the date options the user set
If RadioButton1.Checked = True Then

	'Full date set
	DateFrom = DateTimePicker6.Text
	DateTo = DateTimePicker7.Text
	MsgBox(DateFrom & vbCrLf & DateTo)

ElseIf RadioButton2.Checked = True Then

	'Month + Year set
	DateFrom = "01 " & DateTimePicker6.Text
	DateTo = DateTime.DaysInMonth(Year(DateTimePicker7.Value), Month(DateTimePicker7.Value)) & " " & DateTimePicker7.Text
	MsgBox(DateFrom & vbCrLf & DateTo)

ElseIf RadioButton3.Checked = True Then

	'Year set
	DateFrom = "01 Jan " & DateTimePicker6.Text
	DateTo = "31 Dec " & DateTimePicker7.Text
	MsgBox(DateFrom & vbCrLf & DateTo)

End If

These date strings are then used in combination with the techniques I learned from jmcilhinney's posts.

In some quick testing this seems to work properly and looks promising for me.

Thanks for all input.

If anyone ever has an idea on how to manipulate the date in the date column of a dataview using the rowfilter. I'm open to all suggestions.

Best regards,

Leon

[jmcilhinney]

As you say, the MSDN documentation details what syntax is supported by the DataView.RowFilter property and FORMAT is not part of it. Obviously you need to extract the year out of the date value but there's nothing that will do anything with dates other than:

CONVERT

Description
Converts particular expression to a specified .NET Framework Type.

Syntax
Convert(expression, type)

Arguments
expression -- The expression to convert.

type -- The .NET Framework type to which the value will be converted.


Example: myDataColumn.Expression="Convert(total, 'System.Int32')"

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

So, you use the CONVERT function to get a string from the date, then get the year out of that string. You might then convert that back to a number but there's not really any need; with years that are all four digits the alphabetical order will match the numerical order.

EDIT: Note that the SUBSTRING and LEN functions will be helpful in extracting the last four characters from a string.

[droopy928gt]

Hello jmcilhinney,

Thank you for your reply.

What you write is very interesting. I hadn't thought of that. Clearly my inexperience showing.

I did a very quick test with Convert to string and back using below lines and it's working as such.

.RowFilter = "convert(convert([Start Date], 'System.String'), 'System.DateTime')" & " > '17 Oct 2005'"

.RowFilter = "convert(convert([Start Date], 'System.String'), 'System.DateTime') > '17 Oct 2005'"

I'm gonna dive deeper into it to see what I can do with this. Whatever I find though, the initial problem is solved. My previous posted code implemented is working as I want it, and this looks to be working too when worked out properly.

Thanks to all for the suggestions given. Very much appreciated.

Best regards,

Leon