get year dash month number for sorting column

I have a date field: TRANSACTION_DATE

I would like to return the year and month number separated by a dash to create a new column that would look like (not the month name):

2012 - 001  (January)
2012 - 002 (February)
2012 - 003 (March)
..
...
....
2012 - 011 (November)


Would someone shoe me how I can do this?

Thank you much.
metropiaAsked:
Who is Participating?
 
sdstuberCommented:
try this...
ee.txt
0
 
lluddenCommented:
DECLARE @MyDate DATE = getdate()
SELECT CAST(DATEPART(yyyy,@MyDate) as varchar(4)) + ' - ' + LEFT('00' + CAST(datepart(mm,@MyDate) AS  varchar(2)),3)

Open in new window

0
 
metropiaAuthor Commented:
Thank you, that worked.

What if I do not want to include that column on my display results, but I want to use it to sort the results?

Here is my entire query as right now:

SELECT 
MILL_TYPE, 
(CONVERT(VARCHAR(4),TRANSACTION_MONTH_AND_YEAR,100) + '-' + CONVERT(VARCHAR(4),YEAR(TRANSACTION_MONTH_AND_YEAR))), 
REPLACE(CONVERT(varchar(20), 
(CAST(SUM(TOTAL_RUN) AS money)), 1), '.00', ''), 
cast(year(TRANSACTION_MONTH_AND_YEAR) as varchar) +'-' + right('000' + cast(month(TRANSACTION_MONTH_AND_YEAR) as varchar),3)
FROM RG_MIL_PRODUCTION_REPORT_VW
WHERE TRANSACTION_DATE >= {ts '2011-01-01 00:00:00'} AND TRANSACTION_DATE <{ts '2012-01-01 00:00:00'}
GROUP BY 
(CONVERT(VARCHAR(4),TRANSACTION_MONTH_AND_YEAR,100) + '-' +  CONVERT(VARCHAR(4),YEAR(TRANSACTION_MONTH_AND_YEAR))), 
MILL_TYPE, 
cast(year(TRANSACTION_MONTH_AND_YEAR) as varchar) +'-' + right('000' + cast(month(TRANSACTION_MONTH_AND_YEAR) as varchar),3)

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
simply include the column in the order by but don't select it.

since you are doing a group by, the value that you order by must be part of your grouping
0
 
sdstuberCommented:
note http:#a37787979

the LEFT should be RIGHT as in the first post.

 '00' + '12'  would become  '001'  with LEFT instead of the correct value '012' with RIGHT

I see in your use case you are using RIGHT, that's good. Just clarifying for all readers
0
 
metropiaAuthor Commented:
Cool. Thank you!
0
 
sdstuberCommented:
glad to help,

if you need nothing else, don't forget to close the question.
0
 
metropiaAuthor Commented:
i am about to close this question. and i know this will be unrelated to the original post but you may be willing to throw a hand quickly.

i am trying to remove the 2 decimal places from the amount returned by this query:

CONVERT(varchar, CAST(SUM(TOTAL_RUN) AS money), 1)

I get: 178,373.00

I only need: 178,373

If you can help with this that would be sweet if not, no big deal.

Thank you much!
0
 
sdstuberCommented:
don't cast the sum as money.  cast it as another numeric type
0
 
metropiaAuthor Commented:
I did it like this:

REPLACE(CONVERT(varchar(20), (CAST(SUM(TOTAL_RUN) AS money)), 1), '.00', '')
0
 
sdstuberCommented:
I guess that will work, but easier is to simply NOT use money,  money is why you have 2 decimal places
0
 
metropiaAuthor Commented:
but when i use int, then the formatting is not applied.
0
 
sdstuberCommented:
we're spending more time on the tangent than the original question.

if you want to pursue that line more, how about opening a new question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.