How to Group by Date Time Column Without Taking Time into Consideration

How can I group by date time column without taking time into consideration

Cast/Convert the values to a Date type for your group by.

GROUP BY CAST(myDateTime AS DATE)

Group by date, without the time included BUT keep date as one entry

The column would clearly have a time component (based on the name). Try aggregating on the date component:

SELECT CAST(b.UPSRT_DTTM AS DATE), COUNT(b.cnsmr_id) AS NumberofTimes
FROM UDEFCAT_YOUR_BUDGET b
WHERE b.UPSRT_DTTM >= '2020-01-01' AND Cnsmr_id = '600130'
GROUP BY CAST(b.UPSRT_DTTM AS DATE);

Group by date only on a Datetime column

Cast the datetime to a date, then GROUP BY using this syntax:

SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate);

Or you can GROUP BY the alias as @orlandu63 suggested:

SELECT SUM(foo), DATE(mydate) DateOnly FROM a_table GROUP BY DateOnly;

Though I don't think it'll make any difference to performance, it is a little clearer.

Get multiple max values sorting date time

Use dense_rank() to find the rows with maximum DataValue (partition) by Date and TagName

select *
from (
    select *,
        rn = dense_rank() over (
            partition by left(DateAndTime, 10), 
            TagName 
            order by DataValue desc
        )
    from RTDB.dbo.Env_AI
) t
where rn = 1

How to aggregate the data group by date without using group by?

Maybe concat() function is the point.

select x.*
into #df
from
(
SELECT 1 as id, 'a' as site, 23 as clicks, 1 as approved, '2020' as year, '1' as    month, '1' as day
UNION ALL
SELECT 2 as id, 'a' as site, 1 as clicks, 1 as approved, '2020' as year, '1' as month,     '1' as day
UNION ALL
SELECT 3 as id, 'a' as site, 3 as clicks, 1 as approved, '2020' as year, '3' as month,     '1' as day
UNION ALL
SELECT 4 as id, 'a' as site, 11 as clicks, 1 as approved, '2020' as year, '4' as     month, '1' as day
UNION ALL
SELECT 5 as id, 'a' as site, 345 as clicks, 1 as approved, '2020' as year, '5' as     month, '1' as day
UNION ALL
SELECT 6 as id, 'a' as site, 88 as clicks, 1 as approved, '2020' as year, '6' as     month, '1' as day
UNION ALL
SELECT 7 as id, 'a' as site, 1 as clicks, 1 as approved, '2020' as year, '12' as     month, '1' as day
) x;

select 
    concat(year, '_', month) yyyy_mm
    , sum(clicks) clicks_total
from #df
group by 
    concat(year, '_', month)

How to group by date regardless of time?

This should allow you to group by year month and day

 SELECT group_by_column
      , DATE_FORMAT(created_at, '%Y')
      , DATE_FORMAT(created_at, '%m')
      , DATE_FORMAT(created_at, '%d')
   FROM my_table
  GROUP BY group_by_column

or if you want to do them all together.

 SELECT group_by_column
      , DATE_FORMAT(created_at, '%Y%m%d')
   FROM my_table
  GROUP BY group_by_column

---

Related Topics