none
Linq to Entity to truncate the Date

    Question

  • I am trying to get this functionality in Linq 

    where trunc(created_date) = trunc(sysdate)

    select trunc(sysdate) from dual   = 8/27/2010 (FORMAT) in Oracle.

    Thanks in Advance

     

    Friday, August 27, 2010 4:51 PM

Answers

  • In Sql Server the column type DateTime contains both the Date and the Time also.  Setting up the conversion like I stated will automatically remove the time and just return the date.  It creates this in the sql query, not just application side. 

    The error you previouly posted.  "Method cannot be translated".  Is because your Trunc method is application side, and not an Expression.  The DateTime.Date method can be translated, and that is why I am suggesting it. 

    • Marked as answer by Lavanyr Tuesday, August 31, 2010 5:58 PM
    Tuesday, August 31, 2010 5:30 PM

All replies

  • I dont understand the questions completely.   Most of DateTime methods, instance and static convert to SQL.  So you can do this.

    where date1.Date == date2.Date

    Friday, August 27, 2010 8:15 PM
  • I dont believe Trunc is supported.
    Friday, August 27, 2010 9:50 PM
  • trunc is a specific Oracle PL-SQL function that is not found in either SQL or .NET - so what you need to do is to create a custom trunc function in .NET and use it in your LINQ statements.

    The trunc method in Oracle removes the time values in the date and keeps the date - I have made a similar trunc method in .NET - you can try it

    private DateTime trunc(DateTime date)

    {

    return new DateTime(date.Year, date.Month, date.Day);

    }


    Ali Hamdar (alihamdar.com)
    • Marked as answer by Lavanyr Monday, August 30, 2010 2:23 PM
    • Unmarked as answer by Lavanyr Tuesday, August 31, 2010 3:22 PM
    Sunday, August 29, 2010 12:52 PM
  • Ali,

    The above method is working for this kind of scenarios

    DateTime xy = DateTime.Now;
    Console.WriteLine("The TIME 1ST {0}", xy);

    But when i am trying to Implement in Linq 

     var x = from d in obj.PG_PAYMENT_TRANSACTIONS
                from a in obj.PG_CONTRACT_MASTER
                where trunc(d.CREATED_DATE) == trunc(DateTime.Now) && d.CONTRACT_ABBR == a.CONTRACT
                select new { Contract = a.DESCRIPTION }; 
     Console.WriteLine("Z value {0}", x.FirstOrDefault());

     

    Not Supported Exception Was Unhandled

    LINQ to Entities does not recognize the method 'System.DateTime trunc(System.DateTime)' method, and this method cannot be translated into a store expression.

    Thanks

    Lavanya

    Tuesday, August 31, 2010 3:26 PM
  • DateTime now = DateTime.Now.Date;
    
     var x = from d in obj.PG_PAYMENT_TRANSACTIONS
     from a in obj.PG_CONTRACT_MASTER
     where d.CREATED_DATE.Date == now && d.CONTRACT_ABBR == a.CONTRACT
     select new { Contract = a.DESCRIPTION }; 
     Console.WriteLine("Z value {0}", x.FirstOrDefault());

    Here is applying my first comment.

    You may have to do it like the following, not sure.  It maybe faster also.  I think Linq may auto skip Time in this case it can tell you only comparing Dates.

    DateTime now = DateTime.Now;
    
     var x = from d in obj.PG_PAYMENT_TRANSACTIONS
     from a in obj.PG_CONTRACT_MASTER
     where d.CREATED_DATE.Date == now.Date && d.CONTRACT_ABBR == a.CONTRACT
     select new { Contract = a.DESCRIPTION }; 
     Console.WriteLine("Z value {0}", x.FirstOrDefault());

     

     

    Tuesday, August 31, 2010 4:05 PM
  • LitEnders,

    public static DateTime trunc(DateTime date)
     {
     return new DateTime(date.Year, date.Month, date.Day);
     }

    I am using Trunc Method because in my Oracle Table the time contains MMDDYY and HH:MM:SS.

    If i do as you mention in your comment the  result will null.

     

    Any Help

    Tuesday, August 31, 2010 4:48 PM
  • In Sql Server the column type DateTime contains both the Date and the Time also.  Setting up the conversion like I stated will automatically remove the time and just return the date.  It creates this in the sql query, not just application side. 

    The error you previouly posted.  "Method cannot be translated".  Is because your Trunc method is application side, and not an Expression.  The DateTime.Date method can be translated, and that is why I am suggesting it. 

    • Marked as answer by Lavanyr Tuesday, August 31, 2010 5:58 PM
    Tuesday, August 31, 2010 5:30 PM