Datediff with column ?

[loyal]

hi all

 

I am trying to calculate date with some columns

 

well I have 3 columns in my table

 

1 if for startDate 2 endDate 3 total

 

I want to subtract startDate from endDate and

 

the result as long will be in total as Number

 

here's the code it works with TextBoxes

 

 

 

Try
           Dim d1 As Date = CDate(txtStartDate.Text)
           Dim d2 As Date = CDate(txtEndDate.Text)
           Dim MD As Long = DateDiff( _
           DateInterval.Day, d1, d2)

           txtTotal.Text = MD

       Catch ex As Exception
           MessageBox.Show(ex.Message)

       End Try

 

but how can I implement this on Columns ? please help

 

as my ugly code goes like this <<<<

 

Try
           da.Fill(Ds1, "Table")
           Dim d1 As Long = Ds1.Tables( _
           "Table").Columns("total")
           Dim d2 As Long = Ds1.Tables( _
           "Table").Columns("total")


           Ds1.Tables("Table").Columns( _
           "total").Expression = _
           DateDiff(DateInterval.Day, d1, d2)

       Catch ex As Exception
           MessageBox.Show(ex.Message)

       End Try

[loyal]

with syntax and runtime error please help

[loyal]

Try

With Ds1.Tables("Table")

.Columns("total").Expression = _

"Convert(fd , 'System.Int32') - Convert(ld,'System.Int32')"

End With

Catch ex As Exception 'Invalid Cast from Date to Int32 ?

MessageBox.Show(ex.Message)

End Try

End Sub

[Nerseus]

If you just want to get the value for one row, you can cast the column as a DateTime and then do your DateDiff as normal. Something like:

Dim d1 As Datetime = DirectCast(Ds1.Tables("Table").Columns("total"), DateTime)
Dim d2 As DateTime = DirectCast(Ds1.Tables("Table").Columns("total"), DateTime)
' Now use d1 and d2 to get the DateDiff

 

I may be off on how to use DirectCast - I'm not that familiar with VB.NET.

 

Now if you want a 3rd column to be the DateDiff, it might be a bit harder. An expression column can use functions, but they're limited and I don't think DateDiff is one of them. If you need that, I'd really think about doing it in the query. If you need it dynamic, like what an expression column gives you, you may have to play around with a couple of functions (if there are any Datepart functions or a Month() or Day() function).

 

You could also "fake" an expression column if nothing else works. You can setup a ColumnChanged event on your DataTable and whenever one of the two date columns changes, update a 3rd int column with the date difference. Not as clean as an expression column, but might be your only answer.

 

-Ner