473,320 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Q: Dates and Times Problem

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
11 1578
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
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
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
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
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
Hi Geoff,

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

:-)

Cor
Nov 20 '05 #7
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: PW | last post by:
<rant> Sorry guys, but I just have to whinge. Dates in ASP are a total pain in the butt! I seem to get caught out so many times. I realise its my own fault, but going from the posts in this...
10
by: Brent Webster | last post by:
This problem seems to occur dependant on the computer system settings. I have no issues when the system date is set as M/d/yyyy but I do have a problem with the system date set as d/M/yyyy. I...
5
by: Steve | last post by:
I am currently trying to validate data in an access database. I need to verify that columns containing date information are in the format ddmmyyyy and columns containg time information are in the...
36
by: Lindie | last post by:
The more I read the more confused I get. Too much on dates calulations in the groups. I need to know how often a book has been loaned out over the past year- 52 weeks. My table has Book...
4
by: reidarT | last post by:
I want to show data objects as Y-axis (rows) and dates alng x-axis (fields) Date 01.01.05 02.01.05 03.01.05 ... Obj1 Free Free Occupied Obj2 Free ...
2
by: Duppypog | last post by:
I'm trying to compare a date stored in a database with today's date using an If statement, but it's not returning true. Example, value in database is 11/5/2003 with today being 11/6/2003. Can...
16
by: Dennis D. | last post by:
Hello: Been working on this way too long. DateDiff returns a Long. : Totally counter-intuitive. Subtracting: 0400 - 0345 returns -15 : Wrong, should be +15? Subtracting 02:00 - 00:15 with an...
1
by: Colin Possamai | last post by:
I have a table with 14 days and appointment times. The days are numbered 0 to 13 (fortnight worth). I have a query (FortnightQuotesAppointments) that adds now() to day number to get the date of...
9
by: clintonb | last post by:
I'm looking for a way to calculate the number of days between two dates using standard C++ functions. Would it be as simple as just using the difftime() function and then dividing that result by...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.