Home Forums SQL Server 2008 SQL Server Newbies convert integer datatype as date format and group them by month RE: convert integer datatype as date format and group them by month

  • September 2, 2015 at 1:34 pm

    #1824132

    SQLServerRookie (9/2/2015)

    Thanks for the replies. Looking at the suggestions given I end up with this query:

    SELECT CUSTOMER, MONTH(DATEADD(DAY,DATE,'2004/07/01'))AS MONTH, YEAR(DATEADD(DAY, DATE,'2004/07/01'))AS YEAR, SUM (SALESAMOUNT) AS SALES

    FROM MY_TABLE

    GROUP BY MONTH(DATEADD(day,CDC,'2004/07/01')), YEAR(DATEADD(DAY, CDC, '2004/07/01')), CUSTOMER

    ORDER BY YEAR(DATEADD(DAY, CDC, '2004/07/01')), MONTH(DATEADD(day,CDC, '2004/07/01')), CUSTOMER

    The output shows customer sales grouped by month and then years. I verified the results by picking at random one customer's month of sales and running daily sales for 31 days for the same month and then adding the results. They matched! Life is good!

    Thanks

    Only problem, if 1 represents 2004-07-01, adding 1 to 2004-07-01 will result in 2004-07-02. This will be an issue when you get to the end of each month. You should use 2004-06-30 as the base, or subtract 1 from [Date].