SQL Statement Where DateTime Equals GETDate()...

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

Answer 1

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.

Answer 2

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