By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,061 Members | 1,539 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,061 IT Pros & Developers. It's quick & easy.

Q: Dates and Times Problem

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
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

Nov 20 '05 #2

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

http://msdn.microsoft.com/library/de...ttingtypes.asp

http://msdn.microsoft.com/library/de...matstrings.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)
' get both the date & time
Const format2 As String = "MM/dd/yyy HH:mm:ss" Dim myStringDate As String = myDate.ToString(format2)
Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. . 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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl...
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

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
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



Nov 20 '05 #3

P: n/a
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:uP**************@TK2MSFTNGP10.phx.gbl...
Geoff,
ToString is overloaded, you can pass a format string so myStringDate comes
out in the format you want.

http://msdn.microsoft.com/library/de...ttingtypes.asp
http://msdn.microsoft.com/library/de...matstrings.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)


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


Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:eE**************@tk2msftngp13.phx.gbl...
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

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
> 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
>
>



Nov 20 '05 #4

P: n/a
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

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:uP**************@TK2MSFTNGP10.phx.gbl...
Geoff,
ToString is overloaded, you can pass a format string so myStringDate comes
out in the format you want.

http://msdn.microsoft.com/library/de...ttingtypes.asp

http://msdn.microsoft.com/library/de...matstrings.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)


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


Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:eE**************@tk2msftngp13.phx.gbl...
> 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
>
> "Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
> news:40*********************@news.dial.pipex.com.. .
> > 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
> >
> >
>
>
>



Nov 20 '05 #5

P: n/a
Hi Jay

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

Thanks for all your help.

Geoff

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:eA**************@TK2MSFTNGP11.phx.gbl...
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

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message news:uP**************@TK2MSFTNGP10.phx.gbl...
Geoff,
ToString is overloaded, you can pass a format string so myStringDate comes out in the format you want.

http://msdn.microsoft.com/library/de...ttingtypes.asp

http://msdn.microsoft.com/library/de...matstrings.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)

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

Hope this helps
Jay
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40*********************@news.dial.pipex.com.. .
> 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
>
> "Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in

message
> news:eE**************@tk2msftngp13.phx.gbl...
> > 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
> >
> > "Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
> > news:40*********************@news.dial.pipex.com.. .
> > > 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
> > >
> > >
> >
> >
> >
>
>



Nov 20 '05 #6

P: n/a
Hi Geoff,

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

:-)

Cor
Nov 20 '05 #7

P: n/a
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

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:ek**************@TK2MSFTNGP11.phx.gbl...
Hi Geoff,

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

:-)

Cor

Nov 20 '05 #8

P: n/a
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
Nov 20 '05 #9

P: n/a
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 > #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
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40***********************@news.dial.pipex.com ... 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

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:ek**************@TK2MSFTNGP11.phx.gbl...
Hi Geoff,

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

:-)

Cor


Nov 20 '05 #10

P: n/a
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:u5**************@TK2MSFTNGP12.phx.gbl...
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 >

#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
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40***********************@news.dial.pipex.com ...
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

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:ek**************@TK2MSFTNGP11.phx.gbl...
Hi Geoff,

Thanks answering this, because I could not understand it, so you did

bring me in doubt.

:-)

Cor



Nov 20 '05 #11

P: n/a
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

"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40***********************@news.dial.pipex.com ...
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

"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:u5**************@TK2MSFTNGP12.phx.gbl...
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 >

#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
"Geoff Jones" <ge***@NODAMNSPAM.com> wrote in message
news:40***********************@news.dial.pipex.com ...
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

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:ek**************@TK2MSFTNGP11.phx.gbl...
> Hi Geoff,
>
> Thanks answering this, because I could not understand it, so you did

bring
> me in doubt.
>
> :-)
>
> Cor
>
>



Nov 20 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.