Post reply

Date conversion

  • April 17, 2008 at 8:43 am

    #74893

    Hi,

    Can anyone tell me how to convert a datetime (or smalldatetime) value into MM/YYYY format while still retaining date functionality for sorting?

    I would like to pull data through to an Excel spreadsheet and allow the user to view the data in a pivot table, sorted or filtered by month. The months could encompass multiple years, so years are a factor. I have only been able to come up with this:

    SELECT RIGHT(CONVERT(VARCHAR, GETDATE(), 103), 7)

    While it displays as I want it to, of course it sorts by month, then year, rather than reading as an actual date and sorting by year, then month.

    Thanks for your help!

  • April 17, 2008 at 8:51 am

    #803780

    I apologize...I'm working with SQL Server 2005. I don't know that it makes a difference, but I will post this message there instead.

  • April 17, 2008 at 8:52 am

    #803781

    It might be better to send excel the date itself, and let it display it whichever way it likes. If you want to group things together by month, then simply bringing all of the date values within a given month back to a specific value should work, like for example:

    select dateadd(month,datediff(month,0,getdate()),0)

    That way - you let Excel worry about the formatting, and you retain all datetime functionality.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • April 17, 2008 at 1:55 pm

    #803993

    We generally use CCYYMM format to retain the sorting when grouping aggregates into months.

    SELECT datepart(yyyy,getdate()) * 100 + datepart(mm,getdate())

  • April 21, 2008 at 6:41 am

    #804921

    Thank you! I may be able to use this quite a bit.

Viewing 5 posts - 1 through 4 (of 4 total)

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