Linq idea on date calculation

Question

How to convert this query to linq?

Example: 
`select dateadd(hour, datediff(hour, 0, '2020-05-17 19:20:00' ), 0)`

Result: `'2020-05-17 19:00:00'`

Update question to more detail..

Example Input data:
**DateTimeColumn**
2020-05-17 11:29:12.1234
2020-05-17 19:20:00.2132
2020-05-17 19:30:00.2132
2020-05-17 22:34:22.2134
2020-05-18 11:01:01.1111

Query:
select dateadd(hour, datediff(hour, 0, DateTimeColumn ), 0) as NewTime, Count(*) as Count from Table group by dateadd(hour, datediff(hour, 0, DateTimeColumn ), 0)

Result:

**NewTime**         **Count**
2020-05-17 11:00:00  1  
2020-05-17 19:00:00  2
2020-05-17 22:00:00  1
2020-05-18 11:00:00  1

Purpose: Need a linq query for group by hour, can't use group by datetime.hour due to it will merge hourly data with different date (example the 11 A.M for 17/5 and 18/5 are two different data, can't group together)

You need to group by day **and** by hour, here is an example https://stackoverflow.com/questions/847066/group-by-multiple-columns — colinD, May 17 '20 at 20:55
You aren't showing a query, but in LINQ you can do date math and you can use `EF.Functions` for direct access to SQL date/time functions. What type of LINQ are you using (e.g. LINQ to SQL, EF 6, EF Core 2, EF Core 2.2, EF Core 3.x)? — NetMage, May 18 '20 at 21:41
Hi @NetMage, I am using Linq2SQL now. What you mean LINQ can do date mate? — kchong Hoh, May 19 '20 at 01:11

score 0 · Answer 1 · answered May 19 '20 at 22:31

0

Given,

var baseDate = new DateTime(1900,1,1);

You can use it in LINQ to SQL such as:

var grouped = from r in db
              let hr = baseDate.AddHours(Math.Truncate((r.a_date-baseDate).TotalHours))
              group r by hr into rg
              select new {
                  Hour = rg.Key,
                  Count = rg.Count()
              };

answered May 19 '20 at 22:31

NetMage

26,163
3
34
55

Thank you @NetMage!! May I know what is a_date? – kchong Hoh May 21 '20 at 03:58
why need minus the baseDate? Can you explain more? sorry, I didnt get your concept. – kchong Hoh May 21 '20 at 11:55
@kchongHoh your example had a literal date in it, instead of referencing a field from a database. `a_date` just represents any date field in your database. You would need to update your question with real information to get a matching answer. – NetMage May 21 '20 at 20:37
The `-basedate` is a translation of the `datediff(hour, 0, ` in your SQL. In SQL, the `0` represents a base date to compute from. Do you understand what the SQL does? – NetMage May 21 '20 at 20:38
Thank for your reply. I just don't understand what your LINQ does. – kchong Hoh May 22 '20 at 01:32

Linq idea on date calculation

1 Answers1