DataTable.Select method problem (can't understand DateDiff)

I've got a DataTable object which I want to filter using it's Select method,
but it doesn't recognize the DateDiff function in the filter string (which
is odd considering the DataTable was populated with a query containing the
DateDiff function).

Is there some way around this? Is there a different syntax for doing this
in a datatable's Select method?

- Don

William
Tue Sep 02 11:05:57 CDT 2003

The DataTable Select method (or the DataView Filter property) understands
only (fairly) simple column selection criteria--not TSQL (or SQL) operators
like DateDiff.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Don" <unknown@oblivion.com> wrote in message
news:EG25b.889219$3C2.20490531@news3.calgary.shaw.ca...
> I've got a DataTable object which I want to filter using it's Select
method,
> but it doesn't recognize the DateDiff function in the filter string (which
> is odd considering the DataTable was populated with a query containing the
> DateDiff function).
>
> Is there some way around this? Is there a different syntax for doing this
> in a datatable's Select method?
>
> - Don
>
>

Rebecca
Tue Sep 02 11:18:42 CDT 2003

The syntax for Select is fairly limited. It's the same as the
DataColum.Expression property, and it's documented under that listing. But
you should be able to use normal operators, since dbDateTime is represented
as a .Net DateTime structure.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

"Don" <unknown@oblivion.com> wrote in message
news:EG25b.889219$3C2.20490531@news3.calgary.shaw.ca...
> I've got a DataTable object which I want to filter using it's Select
method,
> but it doesn't recognize the DateDiff function in the filter string (which
> is odd considering the DataTable was populated with a query containing the
> DateDiff function).
>
> Is there some way around this? Is there a different syntax for doing this
> in a datatable's Select method?
>
> - Don
>
>

Don
Tue Sep 02 11:39:13 CDT 2003

"Rebecca Riordan" <rebeccar@attglobal.net> wrote in message
news:#RaGn3WcDHA.3708@tk2msftngp13.phx.gbl...
> The syntax for Select is fairly limited. It's the same as the
> DataColum.Expression property, and it's documented under that listing.
But
> you should be able to use normal operators, since dbDateTime is
represented
> as a .Net DateTime structure.

Is there any way to do what I want without using DateDiff, though? I need
to get the difference in days, weeks, months, and years between the date in
a field and a fixed date as part of the filter.

- Don

Rebecca
Tue Sep 02 11:49:55 CDT 2003

Probably, but I'm having (yet another) stupid day. Can you explain what you
want to do in words of two-syllables or less? <g>

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

"Don" <unknown@oblivion.com> wrote in message
news:RI35b.882251$ro6.17902821@news2.calgary.shaw.ca...
> "Rebecca Riordan" <rebeccar@attglobal.net> wrote in message
> news:#RaGn3WcDHA.3708@tk2msftngp13.phx.gbl...
> > The syntax for Select is fairly limited. It's the same as the
> > DataColum.Expression property, and it's documented under that listing.
> But
> > you should be able to use normal operators, since dbDateTime is
> represented
> > as a .Net DateTime structure.
>
> Is there any way to do what I want without using DateDiff, though? I need
> to get the difference in days, weeks, months, and years between the date
in
> a field and a fixed date as part of the filter.
>
> - Don
>
>

Don
Tue Sep 02 12:00:55 CDT 2003

> > Is there any way to do what I want without using DateDiff, though? I
need
> > to get the difference in days, weeks, months, and years between the date
> > in a field and a fixed date as part of the filter.
>
> Probably, but I'm having (yet another) stupid day. Can you explain what
you
> want to do in words of two-syllables or less? <g>

I will give an example of what I need to do:

"DateDiff('m', [DateField], #Sep 2, 2003#) > [MonthSkipCountField]"

I need to count the number of days/weeks/months/years between a date in a
record and a reference date I get elsewhere. Then I take this value and
compare it with a value stored in a different field in the same record. I
don't really know how else to explain it.

- Don

Rebecca
Tue Sep 02 12:32:43 CDT 2003

Okay, well, I haven't tried this, but what about using the - operator, which
returns a DateTime, and then formatting it for months, years, or whatever?

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

"Don" <unknown@oblivion.com> wrote in message
news:b145b.889345$3C2.20500917@news3.calgary.shaw.ca...
> > > Is there any way to do what I want without using DateDiff, though? I
> need
> > > to get the difference in days, weeks, months, and years between the
date
> > > in a field and a fixed date as part of the filter.
> >
> > Probably, but I'm having (yet another) stupid day. Can you explain what
> you
> > want to do in words of two-syllables or less? <g>
>
> I will give an example of what I need to do:
>
> "DateDiff('m', [DateField], #Sep 2, 2003#) > [MonthSkipCountField]"
>
> I need to count the number of days/weeks/months/years between a date in a
> record and a reference date I get elsewhere. Then I take this value and
> compare it with a value stored in a different field in the same record. I
> don't really know how else to explain it.
>
> - Don
>
>

Don
Tue Sep 02 13:27:45 CDT 2003

"Rebecca Riordan" <rebeccar@attglobal.net> wrote in message
news:OAbL8gXcDHA.856@tk2msftngp13.phx.gbl...
> Okay, well, I haven't tried this, but what about using the - operator,
which
> returns a DateTime, and then formatting it for months, years, or whatever?

I can't use the - operator. It gives me an error saying you can't use it
with dates.

- Don

IbrahimMalluf
Tue Sep 02 14:22:44 CDT 2003

Save yourself some grief Don, instead of filtering an existing rowset,
generate a new rowset where you can actually use T-SQL to give you what you
want.


"Don" <unknown@oblivion.com> wrote in message
news:b145b.889345$3C2.20500917@news3.calgary.shaw.ca...
> > > Is there any way to do what I want without using DateDiff, though? I
> need
> > > to get the difference in days, weeks, months, and years between the
date
> > > in a field and a fixed date as part of the filter.
> >
> > Probably, but I'm having (yet another) stupid day. Can you explain what
> you
> > want to do in words of two-syllables or less? <g>
>
> I will give an example of what I need to do:
>
> "DateDiff('m', [DateField], #Sep 2, 2003#) > [MonthSkipCountField]"
>
> I need to count the number of days/weeks/months/years between a date in a
> record and a reference date I get elsewhere. Then I take this value and
> compare it with a value stored in a different field in the same record. I
> don't really know how else to explain it.
>
> - Don
>
>

Don
Tue Sep 02 14:46:30 CDT 2003

"IbrahimMalluf" <Ibrahim@malluf.com> wrote in message
news:OBQM$SYcDHA.656@tk2msftngp13.phx.gbl...
> Hello Don
>
> Bill's right, no TSQL in select. But you can work around this. I assume
> that you are trying to return rows based on a date range, right? Then
build
> up a Between statement that saitisfies your range requirement.

A Between statement might work, but there may simply be too many cases to
make it worthwhile. It's a very good suggestion and I will look into it.

- Don

Don
Tue Sep 02 14:48:43 CDT 2003

"IbrahimMalluf" <Ibrahim@malluf.com> wrote in message
news:#oIxgaYcDHA.384@TK2MSFTNGP12.phx.gbl...
> Save yourself some grief Don, instead of filtering an existing rowset,
> generate a new rowset where you can actually use T-SQL to give you what
you
> want.

That was essentially my last resort. I was hoping for a quick answer so I
wouldn't have to resort to putting a higher load on the database server, but
if it must be so, then there's little I can do about it. It's not really a
performance critical section of the app I'm working on anyway.

- Don

IbrahimMalluf
Wed Sep 03 22:07:50 CDT 2003

Hello Don

I joust couldn't leave this one alone

You dont have to do it on the Serverside

There is a client side solution

Make a copy of the table then iterate through it removing the rows you want
filtered out using your DateDiff formula

Like this:

Dim iCOUNT As Integer

Dim Myrow As DataRow

Dim Mytable As DataTable = Me._EmployeeData.Tables(0).Copy

For iCOUNT = Mytable.Rows.Count - 1 To 0 Step -1

Myrow = Mytable.Rows(iCOUNT)

If DateDiff(DateInterval.Year, Myrow("Birthdate"), Now) > 50 Then

Mytable.Rows.Remove(Myrow)

End If

Next

this leaves a table with dates less than 50 years ago

Ibrahim




"Don" <unknown@oblivion.com> wrote in message
news:qs65b.882519$ro6.17915740@news2.calgary.shaw.ca...
> "IbrahimMalluf" <Ibrahim@malluf.com> wrote in message
> news:OBQM$SYcDHA.656@tk2msftngp13.phx.gbl...
> > Hello Don
> >
> > Bill's right, no TSQL in select. But you can work around this. I
assume
> > that you are trying to return rows based on a date range, right? Then
> build
> > up a Between statement that saitisfies your range requirement.
>
> A Between statement might work, but there may simply be too many cases to
> make it worthwhile. It's a very good suggestion and I will look into it.
>
> - Don
>
>