locked
SQL Statement Where DateTime Equals GETDate()... RRS feed

  • Question

  • User-507786106 posted

    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

    Monday, July 1, 2019 3:58 PM

Answers

  • User753101303 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 9:16 PM

All replies

  • User753101303 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 9:16 PM
  • User1520731567 posted

    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

    Tuesday, July 2, 2019 9:11 AM