DataTable.Select returns no rows on a date filter

[Guest]

H
I am using the DataTable Select to filter my data on a date column within the data table, as follows

dr_DataRow = m_dt_ScheduleDataTable.Select("StartDate=#" & Format(dte_DiaryDate, "MM/dd/yyyy") & "#"

This would appear to intermittently return rows, and I can't see why it does sometimes and doesn't on other occasions. The data table I have has 136 rows and the date I am initially testing with matches 22 of these rows. On occasions all 22 are reurned, other times just some of these and at other times no rows

I also allow the user to filter on an integer column

dr_DataRow = m_dt_ScheduleDataTable.Select("InterpreterID=" & CType(int_Interpreter, String)

And this never seems to exhibit any problems so I think it must be a date issue. I am passing the string in in US format as you can see, although I am in UK and everything is in UK format, bit if I pass a date string in UK format it never returns rows and throws an error if I pass in a date which does not comply with US format e.g 16/05/2004, therefore leading me to believe that the Data table holds dates in US format regardless of all my system settings

Has anyone seen anthing like this

Thank
Siobha

 

[Jon Skeet [C# MVP]]

I am using the DataTable Select to filter my data on a date column
within the data table, as follows:

dr_DataRow = m_dt_ScheduleDataTable.Select("StartDate=#" &
Format(dte_DiaryDate, "MM/dd/yyyy") & "#")

This would appear to intermittently return rows, and I can't see why
it does sometimes and doesn't on other occasions. The data table I
have has 136 rows and the date I am initially testing with matches 22
of these rows. On occasions all 22 are reurned, other times just some
of these and at other times no rows!

Is it *just* a date column, or is it a DateTime column? My guess is
that it's a DateTime column, and when you're seeing matches, those
matches have a time of midnight. You might want to do a range change,
where it's >= dteDiaryDate and < dteDiaryDate.AddDays(1). That's just a
guess, but it's worth checking out.

And this never seems to exhibit any problems so I think it must be a
date issue. I am passing the string in in US format as you can see,
although I am in UK and everything is in UK format, bit if I pass a
date string in UK format it never returns rows and throws an error if
I pass in a date which does not comply with US format e.g 16/05/2004,
therefore leading me to believe that the Data table holds dates in US
format regardless of all my system settings.

It's not that DataTable holds dates in US format - it's that the
*expression* assumes a US format.