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?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
try this...
ee.txt
0
 
lluddenConnect With a Mentor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
sdstuberConnect With a Mentor Commented:
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.