Filtering Date in where clause of EntityDataSource

Question

I have a EntityDataSource and I have to filter all items older 30 days.

my approach on my MSSQL database:

protected void EntityDataSource1_Selecting(object sender, EntityDataSourceSelectingEventArgs e)
{
    EntityDataSource1.WhereParameters.Clear();    
    EntityDataSource1.Where = "DATEDIFF(day, GETDATE(), it.CreateDate) < 30" ;
}

gives the error: DATEDIFF cannot be resolved into a valid type or function

Is this a T-SQL database, like SQL Server? DATEDIFF() is a T-SQL command. If you're using an Oracle DB, however, that function doesn't exist, and is done as described here: http://stackoverflow.com/questions/28406397/datediff-function-in-oracle — CDove, Jan 20 '17 at 14:19
@user1895086 _TO_DATE cannot be resolved into a valid type or function_ — Toshi, Jan 20 '17 at 14:29
Are you able to do it in the db with a query ? because Entity is really fun but you have to use function that can be converted into a db query, the one you are using specificly — Antoine Pelletier, Jan 20 '17 at 14:42
@AntoinePelletier yes, `SELECT * FROM [User] WHERE DATEDIFF(day, GETDATE(), CreateDate) < 30` works — Toshi, Jan 20 '17 at 14:44
You can use `WHERE it.CreateDate<= NOW() - INTERVAL 1 MONTH;`, only if `it.CreateDate` is type datetime or timestamp — M. Wiśnicki, Jan 20 '17 at 14:44
@M.Wiśnicki `System.Data.EntitySqlException` invalid syntax — Toshi, Jan 20 '17 at 14:47
@M.Wiśnicki `CreateDate` is type of `DateTime` - i think `NOW()` and `INTERVAL` works with MySQL only — Toshi, Jan 20 '17 at 14:48
Try `SqlServer.DATEDIFF(day, GETDATE(), it.CreateDate) < 30"` — M. Wiśnicki, Jan 20 '17 at 14:56

score 1 · Answer 1 · edited May 23 '17 at 10:29

1

You need call SqlServer.DATEDIFF(), use this:

EntityDataSource1.Where = "SqlServer.DATEDIFF(day, GETDATE(), it.CreateDate) < 30" ;

instead

EntityDataSource1.Where = "DATEDIFF(day, GETDATE(), it.CreateDate) < 30" ;

Here you find similar question but with DATEADD function

edited May 23 '17 at 10:29

Community

1
1

answered Jan 20 '17 at 15:01

M. Wiśnicki

6,094
3
23
28

Filtering Date in where clause of EntityDataSource

1 Answers1