April 17, 2008 at 8:52 am
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:53 am
Already answered over here:
http://www.sqlservercentral.com/Forums/Topic486502-8-1.aspx#bm486513
----------------------------------------------------------------------------------
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 9:02 am
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
I actually had coded that way initially, to just bring back the first day of the month. But the problem I'm having is formatting the pivot table in Excel. I can't get rid of the day; it's in M/D/YYYY format.
April 17, 2008 at 9:19 am
Vicki Peters (4/17/2008)
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)I actually had coded that way initially, to just bring back the first day of the month. But the problem I'm having is formatting the pivot table in Excel. I can't get rid of the day; it's in M/D/YYYY format.
That's when you format cells in Excel to show you only month and year..... you'll notice that you can pick all of the date columns or rows, and format them in one shot...
----------------------------------------------------------------------------------
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 9:24 am
Yes, but that isn't working in my pivot table, when the column I'm trying to format is a grouping (one of the row labels). I can format the values column, where I'm pulling in dollars. But not the row labels.
April 17, 2008 at 9:32 am
Vicki Peters (4/17/2008)
Yes, but that isn't working in my pivot table, when the column I'm trying to format is a grouping (one of the row labels). I can format the values column, where I'm pulling in dollars. But not the row labels.
What I'm trying to tell you is - yes, you can. When you have the pivottable highlighted/active, you should see a blue box surrounding the column labels...clicking on that in just the right way highlights all of the row labels, which can then be formatted using the regular "format cells" options.
By highlighting that row label range, any new rows would also get that format....
----------------------------------------------------------------------------------
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 9:45 am
Thank you for your time, I'll play with it. I still can't get consistent formatting between the row label, the subtotal label, and the values in the filter drop-down. If I can't get them all in the mm/yyyy format, I will just show the first of the month and be done with it. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply