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

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

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
 
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.
__________________________________
 
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...
 
Rebecca Riordan said:
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.
Click to expand...

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
 
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...
 
Is there any way to do what I want without using DateDiff, though? I
need
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>
Click to expand...

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
 
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 said:
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>
Click to expand...

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
Click to expand...
 
Rebecca Riordan said:
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?
Click to expand...

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

- Don
 
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 said:
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>
Click to expand...

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
Click to expand...
 
IbrahimMalluf said:
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.
Click to expand...

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
 
IbrahimMalluf said:
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.
Click to expand...

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
 
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
 
Back
Top