Q: Dates and Times Problem

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

Geoff Jones

Hi

I have a table that has a column with Date types.

I am trying to view certain rows in the table using a DataView.

Using the filter, I can view the rows with, for example, the date equal to
something e.g.

vue.RowFilter = "Date = #01/01/03#"

however, when I try something like

vue.RowFilter = "Date > #01/01/03# AND Date < #02/01/03#" I get an empty set
although there are dates in this value!

Also, I'd like to retrieve rows within certain dates AND times, so I'd like
to do something like:

vue.RowFilter = "Date > #01/01/03 05:03:00# AND #02/01/03 12:00:06#"

However, I must be getting the syntax wrong because again I'm not getting
any rows.

Hope somebody can help.

Thanks in advance

Geoff
 
Geoff,
I am not able to reproduce this.

Using the following code (using the Northwind SQL Server sample database):
Dim orders As New DataView(customerDataSet.Tables("Orders"))
orders.RowFilter = "OrderDate > #2/25/1998# and OrderDate <
#2/27/1998#"
For Each order As DataRowView In orders
Debug.WriteLine(order!CustomerID, "CustomerID")
Debug.WriteLine(order!OrderId, "Order")
Debug.WriteLine(order!OrderDate, "OrderDate")
Next

Remember that DateTime columns contain both a Date & a Time value, for the
start Date #2/25/1998# the date will be included, while the end date
#2/27/1998#, I find its easier to go to the next day... So the above
actually displays the 25th & 26th, but not the 27th... You can include time
such as:

orders.RowFilter = "OrderDate > #2/25/1998# and OrderDate <
#2/26/1998 12:00 PM#"

or

orders.RowFilter = "OrderDate > #2/25/1998# and OrderDate <
#2/26/1998 13:00#"

I get a handful of rows displayed...

Remember the date/time format is US (month/day/year) in date literals.

Hope this helps
Jay
 
Hi Jay

I think this may be a problem with format. I am converting the date object
into a string and during the process the month and day are swapped. This is
why my sort isn't working i.e.

Dim myDate As Date = whatever

Dim myStringDate As String = myDate.ToString() ' This causes the problem

Can anybody suggest a work round?

Geoff
 
Geoff,
ToString is overloaded, you can pass a format string so myStringDate comes
out in the format you want.

http://msdn.microsoft.com/library/d...y/en-us/cpguide/html/cpconformattingtypes.asp

http://msdn.microsoft.com/library/d...s/cpguide/html/cpcondatetimeformatstrings.asp

If myStringDate is going to be as part of the RowFilter I would use
something like:

' get the date only
Const format1 As String = "MM/dd/yyy"
Dim myStringDate As String = myDate.ToString(format1)
Click to expand...

' get both the date & time
Const format2 As String = "MM/dd/yyy HH:mm:ss"
Dim myStringDate As String = myDate.ToString(format2)
Click to expand...

Hope this helps
Jay
 
Hi again

Well, I appear to be getting there Jay. After your help, I can now sort in
terms of date but it doesn't seem to take the time into account!!!

So, for example, I'm using something like:

vue1.RowFilter = "MyDate < #02/05/2004 14:00:00#

which gives all the dates before 02/05/2004 BUT it doesn't take any notice
of the time.

Can you help?

Geoff
 
Geoff,
I'm really not sure what to offer, as every thing I try works as expected.

' Continuing the Northwind sample...
For Each order As DataRow In
customerDataSet.Tables("Orders").Select("OrderDate > #2/25/1998# and
OrderDate <= #2/26/1998 12:00#")
Debug.WriteLine(order!CustomerID, "CustomerID")
Debug.WriteLine(order!OrderId, "Order")
Debug.WriteLine(order!OrderDate, "OrderDate")
order!OrderDate = #7/12/2004 20:27#
Next
Dim orders As New DataView(customerDataSet.Tables("Orders"))
orders.RowFilter = "OrderDate > #7/12/2004 20:00# and OrderDate <
#7/12/2004 21:00#"
For Each order As DataRowView In orders
Debug.WriteLine(order!CustomerID, "CustomerID")
Debug.WriteLine(order!OrderId, "Order")
Debug.WriteLine(order!OrderDate, "OrderDate")
Next

The second for each above displays all the rows that the first for each
modified.

Can you actually post a full sample (15 to 20 lines) that demonstrates the
problem, or if you like you can email me your code & data that demonstrates
your problem.

Note to lurkers I only respond to private emails that I request the person
send me. Thanks for understanding.

Hope this helps
Jay
 
Hi Jay

It was a silly mistake on my end I'm afraid. You're code works beautifully!

Thanks for all your help.

Geoff
 
Hi Geoff,

Thanks answering this, because I could not understand it, so you did bring
me in doubt.

:-)

Cor
 
Hi guys

There was one problem that I didn't solve, and that was when the date and
times were in different columns. No matter how much I tried, I couldn't
select the rows I wanted i.e. it merely selected via date. For example, I'd
try to use this:

vue.RowFilter = "Date > #01/01/03# AND Time > #13:05:09# AND Date <
#02/01/03# AND Time < #12:00:06#"

where Date and Time are the column names. However, now I look at this, I can
see that it is ambiguous.

Maybe somebody could tell me how to select the records assuming the date and
time are in different columns?

Thanks in advance

Geoff
 
Hi Geoff,

You mean how to realize this?

vue.RowFilter = "(Date > #01/01/03# AND Time > #13:05:09#) AND (Date
#02/01/03# AND Time < #12:00:06#)"

Cor
 
Geoff,
If possible I would put both in a single column, possible a computed column,
as matching on two columns is very difficult.

You would need something like (untested):
vue.RowFilter = "(Date > #01/01/03# or (Date = #01/01/03# AND Time >
Click to expand...
#13:05:09#)) AND (Date < #02/01/03# or (Date = #02/01/03# AND Time <
#12:00:06#))"

Basically if the date is the day of interest you want to check the time,
otherwise you want to check the date.

Watch wrapping.

Hope this helps
Jay
 
Hi Jay

Yes, I was coming round to the idea of creating a new column with both items
in it. However, I am interested in your comment about "computed". I was
going to take the date and time as strings, concatenate them, and then
convert to a CDate and then place in the new column. However, if you have an
easier solution I'd be very interested in it.

Many thanks again for your continuing support.

Geoff
 
Geoff,
Assuming your datatable has a Date and a Time column, you "should" be able
to:

Dim table As DataTable
table.Columns.Add("dateTime", GetType(DateTime), "date + time")

However the DataSet OOM, just like VB.NET doesn't understand how to add a
date & a time directly.

I tried using Convert, however that did not work either:

table.Columns.Add("dateTime", GetType(DateTime),
"convert(convert(date, 'System.String') + ' ' + convert(time,
'System.String'), 'System.DateTime')")

Unfortunately you'll either need to manually update each row or using the
RowChanging event possible.

Hope this helps
Jay
 
Back
Top