Defining DateDiff for a calculated column in a datatable

I have the column DateTimeExpired, and I would like to create another column called "Expired" which will show "Yes" or "No" according to the expiration date - "Yes" if the date has already passed.

I wrote this:

DataColumn colExpirationDate = new DataColumn("DateTimeExpired", typeof(DateTime));
DataColumn colExpired = new DataColumn("Expired", typeof(string), "IIF(DateDiff(DateTimeExpired, date())>= 0,'No','Yes')");

But 开发者_StackOverflowI get an exception "The expression contains undefined function call DateDiff()."

(please note that I always want to get the row, no matter if it's expired or not)

How do I set the column's text to the correct form?

All you have to do is convert your two date columns using Date.ToOADate to doubles then subtract.

I use ToOADate to populate hidden columns for each date column.

As M.A Hanin pointed out in his comment, it looks like DateDiff cannot be used in DataColumn Expressions. You could try building the calculated column into the underlying table instead (if you are using MS Sql or similar)

edit: There is no function to get 'today', but assuming that the DataColumn you are adding will only exist for a few hours, you could build in todays date as a constant, and then use comparison operators instead of DateDiff

Try this:

DataColumn colExpirationDate = new DataColumn("DateTimeExpired", typeof(DateTime));
DataColumn colExpired = new DataColumn("Expired", typeof(string),
    String.Format("IIF(DateTimeExpired > #{0}#,'No','Yes')",
    DateTime.Now.ToString("dd/MMM/yyyy")));

Note this will only work if your DataColumn is only retained in memory for less than a day.