Solved

get year dash month number for sorting column

Posted on 2012-03-30
13
426 Views
Last Modified: 2012-03-30
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.
0
Comment
Question by:metropia
  • 7
  • 5
13 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 475 total points
ID: 37787965
try this...
ee.txt
0
 
LVL 18

Assisted Solution

by:lludden
lludden earned 25 total points
ID: 37787979
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
 

Author Comment

by:metropia
ID: 37788023
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37788040
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 475 total points
ID: 37788044
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
 

Author Comment

by:metropia
ID: 37788069
Cool. Thank you!
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 73

Expert Comment

by:sdstuber
ID: 37788198
glad to help,

if you need nothing else, don't forget to close the question.
0
 

Author Comment

by:metropia
ID: 37788316
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37788418
don't cast the sum as money.  cast it as another numeric type
0
 

Author Comment

by:metropia
ID: 37788687
I did it like this:

REPLACE(CONVERT(varchar(20), (CAST(SUM(TOTAL_RUN) AS money)), 1), '.00', '')
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37788704
I guess that will work, but easier is to simply NOT use money,  money is why you have 2 decimal places
0
 

Author Comment

by:metropia
ID: 37788837
but when i use int, then the formatting is not applied.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37788849
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now