SQL Statement Where DateTime Equals GETDate()... [Answered]RSS

• • Reply
  slimbunny

  Participant

  858 Points

  1472 Posts

  SQL Statement Where DateTime Equals GETDate()...

  Jul 01, 2019 03:58 PM|slimbunny|LINK

  How do I convert the following statement into a C# MVC Linq statement?

  SELECT * FROM tablename

  WHERE RecordID = 200 AND EmployeeID = 3 AND Cast(CreatedDate as DATE) = (CAST(GETDATE() AS DATE)

  It works in SQL but unable to compose in LINQ, please help

  A Lifetime ASP.NET DEVELOPER
• • Reply
  PatriceSc

  All-Star

  47220 Points

  17437 Posts

  Re: SQL Statement Where DateTime Equals GETDate()...

  Jul 01, 2019 09:16 PM|PatriceSc|LINK

  Hi,

  My personal preference is to use something such as :

  var today = DateTime.Today;
  var tomorrow= today.AddDays(1);
  var qry = db.Items.Where(o => o.MyDateTime >= today && o.MyDateTime <tomorrow);
  

  Even with SQL I'm using something similar so that an index on the DateTime column could still be used.

• • Reply
  Yuki Tao

  Contributor

  3700 Points

  1431 Posts

  Re: SQL Statement Where DateTime Equals GETDate()...

  Jul 02, 2019 09:11 AM|Yuki Tao|LINK

  Hi slimbunny,

  I find you use Cast(xxx as DATE),

  If your format of CreatedDate is so special that be different with DateTime.Today,they can't compare with each other and will affect your results.

  So,combining with what @PatriceSc said and use toString() method to convert a format which you want.

  For example:

      var today = DateTime.Today.ToString("yyyy-M-d");//2019-7-2
      var aa = db.tablename.Where(x => x.RecordID == 200 && x.EmployeeID ==3 && x.CreatedDate == today).ToList();

  More details about date/time format string,you could refer to：

  https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

  Best Regards.

  Yuki Tao

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.