Last post Jul 02, 2019 09:11 AM by Yuki Tao
Participant
858 Points
1472 Posts
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
All-Star
48490 Points
18071 Posts
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.
Contributor
3710 Points
1431 Posts
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
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
All-Star
48490 Points
18071 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 :
Even with SQL I'm using something similar so that an index on the DateTime column could still be used.
Contributor
3710 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
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.