How can I convert this to numeric

Question

Here is part of sql code that we are using to gather totals
for a month/year (month_status) :

SELECT COUNT (activity_ID) AS totalCount, 
 RTRIM(CONVERT(CHAR(2),DATEPART(month,a.activity_date),
110))+'/'+ CONVERT(CHAR(4), RIGHT(DATEPART(year,a.activity_date),
2), 110) AS Month_Status,
 DATEPART(MONTH, a.activity_date) AS MONTH, DATEPART(YEAR,
a.activity_Date) AS YEAR

The month_status is mm/yy and is used as the x axis label for
the chart.
The order by statement shows 10/08, 11/08, 12/08, 4/08, 5/08,
6/08. 7/09, 8/08, 9/08

How can this be converted to numeric so that the output
display in numeric order : 
4/08, 5/08, 6/08, 7/08, 8/08, 9/08, 10/08, 11/08, 12/08
??

-__cfSearching__- · Answer

> CONVERT( CHAR(2), DATEPART(month,a.activity_date),
> The order by statement shows 10/08, 11/08, 12/08, 4/08, 5/08, 6/08. 7/09, 8/08, 9/08

You did not post the ORDER BY clause. But I suspect it is because you are sorting the results by a "string". Strings are sorted differently than date/time and numeric values. Once you convert() the activity_date month and years to a character string like "4/08" or "10/08", ms sql no longer recognizes it as either a number or date. So it sorts them as strings, producing a different order than you are expecting.

Since DatePart returns a number, try ordering by the numeric year and month.

...
ORDER BY
DATEPART(year, a.activity_Date),
DATEPART(month, a.activity_Date)

> RTRIM(CONVERT(CHAR(2),DATEPART(month,a.activity_date), 110))

Style (ie "110") is used for date/time values. So I do not think is valid there and is probably ignored. Just convert the month and year number to a varchar:

CONVERT(varchar, datepart(month, a.activity_date)) +'/'+ ....

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded