SQL Server Performance

Help with Dating

Discussion in 'SQL Server Reporting Services' started by RonM, Sep 8, 2004.

  1. RonM New Member

    I am trying to group by date (month/year) but the data I am querying is in month/day/year/time.

    I changed the format of that cell to MM/yyyy and it displays the date in the desired result. But it does not group them, I assume because the grouping is being applied prior to the format change. So none of the dates are common becase of the time.

    So how do I do this? Can I do it in the query, I thought about LEFT but I still don't want the day which is in the middle of the 2 pieces of info I need so that won't work.
  2. sundeip New Member

    Try This code, this may help u.

    Select
    Cast(Month(Dat) As Varchar(2))+'/'+ Cast(Year(Dat) As Varchar(4)) ,
    Sum(Cnt)
    From Tst
    Group By
    Cast(Month(Dat) As Varchar(2))+'/'+ Cast(Year(Dat) As Varchar(4))

    Thanks
    Sandy
  3. sundeip New Member

    This Can also solve your purpose.

    Select
    SUBSTRING(CONVERT(VARCHAR(20),DAT,105),4,10) As MMYY,
    Sum(Cnt)
    From Tst
    Group By SUBSTRING(CONVERT(VARCHAR(20),DAT,105),4,10)



    Sandy

    (DB Developer)
  4. rfl New Member

    How about just grouping by year and month?

    use pubs
    select year(ord_date), month(ord_date), count(*)
    from sales
    group by year(ord_date), month(ord_date)

    -rl
  5. ChrisFretwell New Member

    And if you dont mind it in yyyymm format, I prefer
    convert(char(6),order_date,112)

    works great for order by and can always make it back into a date field format if needed later.

Share This Page