Post reply

Converting to Month-Year Format

  • February 23, 2009 at 12:30 am

    #72685

    Dear Friends,

    I have hourly data with Date as "2008-11-30 00:00:00:000" format.

    I want to convert this in to YYYY-MM format and group it for some reports.

    Regards

    Sunil

  • February 23, 2009 at 7:11 am

    #948562

    First thing I do when I learn any new language is to lookup and read about all the functions available. Lookup CONVERT in Books Online (comes free with SQL Server) to see how the following works...

    SELECT CONVERT(CHAR(7),GETDATE(),120)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 24, 2009 at 8:54 am

    #949454

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

  • February 24, 2009 at 12:45 pm

    #949597

    Jeff Moden (2/23/2009)

    Books Online (comes free with SQL Server)

    Well put. I'll be plagiarizing that in the future. 😀

    -- You can't be late until you show up.

  • February 25, 2009 at 5:55 am

    #949905

    Hi There,

    Here is my query....I am getting Daily Sum of MINUTS.

    I would likt to make it as Monthly Sum if possible.

    SELECT CONVERT(VARCHAR,[Date],105) as DATE,

    SUM([MINUTS]) AS MINUTS

    FROM [ROUTS].[dbo].[DEC08]

    WHERE ([ROUTE] LIKE 'S1G1'

    or [ROUTE] LIKE 'S1G2'

    or [ROUTE] LIKE 'S2G1'

    or [ROUTE] LIKE 'S2G2'

    and

    [Date] >= convert(datetime,'01-12-2007',105)

    and [Date] < convert(datetime,'01-01-2009',105)

    GROUP BY CONVERT(VARCHAR,[Date],105)

    ORDER BY CONVERT(DATETIME,CONVERT(VARCHAR,[Date],105),105)

    Rgds

    Sunil

  • February 25, 2009 at 6:14 am

    #949923

    Sunil Kumar (2/25/2009)

    Hi There,

    Here is my query....I am getting Daily Sum of MINUTS.

    I would likt to make it as Monthly Sum if possible.

    SELECT CONVERT(VARCHAR,[Date],105) as DATE,

    SUM([MINUTS]) AS MINUTS

    FROM [ROUTS].[dbo].[DEC08]

    WHERE ([ROUTE] LIKE 'S1G1'

    or [ROUTE] LIKE 'S1G2'

    or [ROUTE] LIKE 'S2G1'

    or [ROUTE] LIKE 'S2G2'

    and

    [Date] >= convert(datetime,'01-12-2007',105)

    and [Date] < convert(datetime,'01-01-2009',105)

    GROUP BY CONVERT(VARCHAR,[Date],105)

    ORDER BY CONVERT(DATETIME,CONVERT(VARCHAR,[Date],105),105)

    Rgds

    Sunil

    What is the datatype of the DATE column? And what makes you thing that sorting by dd-mm-yyyy is actually going to mean anything?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 25, 2009 at 6:49 am

    #949941

    You were previously given sufficient advice that should have enabled you to amend your own query, but I've done it for you here, plus tidied up a few details. I'm assuming that the [Date] column is of type datetime.

    SELECT CONVERT(CHAR(7), [Date], 120) AS [DATE],

    SUM([MINUTS]) AS [MINUTS]

    FROM [ROUTS].[dbo].[DEC08]

    WHERE ([ROUTE] IN ('S1G1', 'S1G2', 'S2G1', 'S2G2'))

    AND ([Date] >= CONVERT(datetime, '20071201'))

    AND ([Date] < CONVERT(datetime, '20090101'))

    GROUP BY CONVERT(CHAR(7), [Date], 120)

    ORDER BY CONVERT(CHAR(7), [Date], 120)

  • September 5, 2009 at 6:59 am

    #1049734

    Hi SSC Eights!,

    Thanks . Your solution was excellent.

    Sorry for the late.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply