Format date for SQL query aggregating date

Question

I have this query which pulls the data correctly but I'd like the timestamp field (TIM) to be in another format than Day(TIM). But I get errors because the query is aggregating that way.

SELECT
DAY(TIM) As 'Day',
[G.TRU] = SUM(case when SEC = 'TRU' and TYP = 'GEO' then 1 else 0 end),
[G.LOA] = SUM(case when SEC = 'LOA' and TYP = 'GEO' then 1 else 0 end),
[G.SEA] = SUM(case when SEC = 'SEA' and TYP = 'GEO' then 1 else 0 end),
[R.SEA] = SUM(case when SEC = 'SEA' and TYP = 'RTE' then 1 else 0 end),
TOTAL  = COUNT(TIM)
FROM AGEO
WHERE SRC = 'EW'
GROUP BY DAY(TIM)

Result:

 Day   G.TRU    G.LOA   G.SEA   R.SEA   TOTAL
-----------------------------------------------
 25      2        4       14      1      21
 26      3        0        2      9      14
-----------------------------------------------

I'd prefer the 25 and 26 to be in a YYYY-MM-DD format. Is there a way?

It seems like basically you want to know [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — Andriy M, Apr 27 '12 at 17:20

Jon Skeet · Accepted Answer · 2012-04-27T17:21:13.600

1

No - you're grouping by DAY(TIM) so there could be various different months and years giving the same day. For example, suppose you had two rows, one on the first of January and one on the first of February - they will be aggregated together, so which one would you expect to retrieve for that result row?

EDIT: Okay, I'm not a SQL guy, but I think you want something like:

SELECT
    CONVERT(Date, TIM) As TIMDATE,
    [G.TRU] = SUM(case when SEC = 'TRU' and TYP = 'GEO' then 1 else 0 end),
    [G.LOA] = SUM(case when SEC = 'LOA' and TYP = 'GEO' then 1 else 0 end),
    [G.SEA] = SUM(case when SEC = 'SEA' and TYP = 'GEO' then 1 else 0 end),
    [R.SEA] = SUM(case when SEC = 'SEA' and TYP = 'RTE' then 1 else 0 end),
    TOTAL  = COUNT(TIM)
FROM AGEO
WHERE SRC = 'EW'
GROUP BY TIMDATE

edited Apr 27 '12 at 17:21

answered Apr 27 '12 at 17:09

Jon Skeet

1,421,763
867
9,128
9,194

yes, that's a mistake then. I want to group by day on the timestamp field rather than aggregate by integer (1-31). – greener Apr 27 '12 at 17:14
@greener: See my edit. It may not be *exactly* right, but I think the gist is correct. The syntax around using the aggregation value for the grouping may well be wrong, but fundamentally you want to convert TIM to a date. – Jon Skeet Apr 27 '12 at 17:22
1

Indeed, I just played around and found the same answer `CONVERT(date,TIM,112)` Thanks! – greener Apr 27 '12 at 17:22

Format date for SQL query aggregating date

1 Answers1