metropia
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
since you are doing a group by, the value that you order by must be part of your grouping
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool. Thank you!
glad to help,
if you need nothing else, don't forget to close the question.
if you need nothing else, don't forget to close the question.
ASKER
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!
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!
don't cast the sum as money. cast it as another numeric type
ASKER
I did it like this:
REPLACE(CONVERT(varchar(20 ), (CAST(SUM(TOTAL_RUN) AS money)), 1), '.00', '')
REPLACE(CONVERT(varchar(20
I guess that will work, but easier is to simply NOT use money, money is why you have 2 decimal places
ASKER
but when i use int, then the formatting is not applied.
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.
if you want to pursue that line more, how about opening a new question.
ASKER
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:
Open in new window