rowFilter and dates

[Richnl]

Hi,
I want to filter on dates with the use of rowfilter
The database column is off type SmallDateTime

The problem is that the dates are not interpeted right
I try'd several variations like dim theDate as string=dtp1.Value.ToShortDateString
If I do > 7-8-2007,
I also get 4-8-2007

 Dim sCol As String = Me.cboColumns.SelectedItem.ToString()
            Dim theDate As DateTime= dtp1.Value (DateTimePicker)
            dv.RowFilter = "" & sCol & " > #" & theDate & "#"

thanks in adv, Richard

 

[Reaction]

How about you apply the Formating you want to the Dataset using DataColumn Expressions to accomplish this? I can not think of it offheart but if you research DataColumn Expressions you should come up with a better way of doing this that way.

Reaction

 

[Richnl]

What exactly are you saying?
You mean making an extra column to reformat the date column in a way that rowfilter will accept it.

Isn't there a more straightforward way off doing it
some type off conversion?
Is it a culture thing or what?

 

[Reaction]

Not an extra column. I mean format the column using the DataColumn Expression method.

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

I had to research this this past week and I know you can handle formatting needed in your display with this.

Reaction

 

[Richnl]

ok, hope that will work

I will look into that sometime later

 

[Richnl]

I was missing some single quotes

[SIZE=2]
dv.RowFilter = [/SIZE][SIZE=2][COLOR=#a31515]""[/COLOR][/SIZE][SIZE=2] & sKol & [/SIZE][SIZE=2][COLOR=#a31515]" > '#"[/COLOR][/SIZE][SIZE=2] & deDatum & [/SIZE][SIZE=2][COLOR=#a31515]"#'"

[/COLOR][/SIZE]

It was more luck then understanding that I found this out.

Anyway, I would still want to hear about your convert solution
because I am not familiar with that.

 

[Richnl]

It still wasn't working ok, because
> 3-3, still gave me 3-3

I changed the code line by adding a time value so that it more reflects off what I see in the database data column
I also changed the format off the datetimepicker to short

[SIZE=2]
crit1 = [/SIZE][SIZE=2][COLOR=#a31515]""[/COLOR][/SIZE][SIZE=2] & sCol & [/SIZE][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][SIZE=2] & sOperator & [/SIZE][SIZE=2][COLOR=#a31515]" '#"[/COLOR][/SIZE][SIZE=2] & theDate & [/SIZE][SIZE=2][COLOR=#a31515]" 0:00:00#'"

[/COLOR][/SIZE]

 

[cjard]

The only way that a date filter of any kind will work in the client side, is to use a format of:

MM/dd/yyyy hh:mm:ss

(i.e. american format)

so any row filter text needs to be like:


.Filter = String.Format("[dateColumn] OPERATOR #{0:MM/dd/yyyy hh:mm:ss}#", datePicker.Value)

valid operators are > >= = <= <



Because dates represent a certain point in time and are essentially numeric floating point, if you want all records for a certain day:

.Filter = String.Format("[dateColumn] >= #{0:MM/dd/yyyy hh:mm:ss}# AND [dateColumn] < #{1:MM/dd/yyyy hh:mm:ss}#", datePicker.Value.Date, datePicker.Value.AddDays(1).Date)

i.e. it has to be a range, greater than or equal to midnight today, and less than or equal to midnight tomorrow

The .Date part of the calls ensure that the time is chopped to midnight



In a date, the number of days since the epoch is the integral part, and the time since midnight is the fractional part

 

[Richnl]

I try'd this, but it gave me ...2007/12:00:00 for the date instead off 00 for the houers. As result the filter finds nothing

  crit1 = String.Format("[ " & sCol & "] " & sOperator & " #{0:MM/dd/yyyy hh:mm:ss}#", theDate)

[B]The old one is working ok, as far as I can see for now[/B]
 crit1 = "" & sCol & " " & sOperator & " '#" & theDate & " 0:00:00#'"

 

[cjard]

I try'd this, but it gave me ...2007/12:00:00 for the date instead off 00 for the houers. As result the filter finds nothing

  crit1 = String.Format("[ " & sCol & "] " & sOperator & " #{0:MM/dd/yyyy hh:mm:ss}#", theDate)

[B]The old one is working ok, as far as I can see for now[/B]
 crit1 = "" & sCol & " " & sOperator & " '#" & theDate & " 0:00:00#'"

I dont think youre quite getting the purpose of string.Format.. We dont put String concatenation inside a string.format:

string.Format("[{0}] {1} #{2:MM/dd/yyyy HH:mm:ss}#", sCol, sOperator, theDate)

Note, i have swapped hh for HH, which is 12 -> 24 hours so you now shouldnt get 12:00:00 for the time.. sorry about that.

Note that your old one has ' ' around the # # around the date.. um, that's kinda confusing, and i'm surprised to hear that vb is understanding it correctly.