Adding a Month Name Sort within T-SQL Code

Question

Could you please help me hardcode into the T-SQL SELECT statement below a CalendarMonth Sort so when someone queries the data it comes out Jan, Feb not Apr, Aug. I have already done this for the FinancialMonth row but struggling with the CalendarMonth.

BEGIN 

INSERT INTO Time3(Date,CalendarYear,CalendarMonth,FinancialYear,FinancialMonth)

SELECT 
@StartDate Date,YEAR(@StartDate) AS CalendarYear,
DATENAME(MONTH,@StartDate) AS CalendarMonth, 
YEAR(Dateadd(MONTH,-3,@StartDate)) AS FinancialYear,
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth
SET @StartDate = DATEADD(dd,1,@StartDate)

END

[check here](http://stackoverflow.com/questions/8314310/convert-month-name-to-month-number-function-in-sql) — revoua, Feb 08 '13 at 10:38
You've got a `Date` column in your table. If it's a `date` or `datetime` column, you can use `ORDER BY Date` whenever you need the sorting while querying that table. — Andriy M, Feb 08 '13 at 11:04

score 2 · Answer 1 · answered Feb 08 '13 at 10:53

2

Try this:

ORDER BY MONTH(@StartDate)

MONTH is the function which returns an integer that represents the month of the specified date. http://msdn.microsoft.com/ru-ru/library/ms187813.aspx

answered Feb 08 '13 at 10:53

Roman Badiornyi

1,509
14
28

I see that you select static scalar data, without table, from your query you will always have one record. Please give more information about your problem. BTW: ORDER BY clause comes in the end of the query, it's the last phase in SQL and you can use aliased or auto calculated columns there... – Roman Badiornyi Feb 08 '13 at 11:07
I need to hardcode into my CalendarMonth statement a sort function that provides someonw who queries the data Jan,Feb not Apr, Aug I aceived something smiliar with the FinancialMonth: 'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth – wafw1971 Feb 08 '13 at 11:23
I am also very new to SQL and do not understand all the terminology yet. – wafw1971 Feb 08 '13 at 11:25
As I understand - you add this statement to Time3 table. Tables are relational, this mean that there data isn't sorted (without indexes). You need to sort data when you select it from table using ORDER BY clause. Read more here: http://msdn.microsoft.com/en-us/library/ms188385.aspx – Roman Badiornyi Feb 08 '13 at 11:31
I cannot get the ORDER BY clause to work, it is probably something I am doing. Thanks for you time. – wafw1971 Feb 08 '13 at 11:40

score 0 · Answer 2 · answered Feb 08 '13 at 10:32

0

simply add an extra column to your query that looks like this, just replace @Month with your variable:

CASE @Month
  WHEN 'January' THEN 1
  WHEN 'February' THEN 2
  WHEN 'March' THEN 3
  WHEN 'April' THEN 4
  WHEN 'May' THEN 5
  WHEN 'June' THEN 6
  WHEN 'July' THEN 7
  WHEN 'August' THEN 8
  WHEN 'September' THEN 9
  WHEN 'October' THEN 10
  WHEN 'November' THEN 11
  WHEN 'December' THEN 12
END as MonthOrder

and then in your ORDER BY clause, order by this column

answered Feb 08 '13 at 10:32

SQLGuru

1,099
5
14

Is there a way of adding it to the Select query like I achieved with the FinancialMonth without having to write a long CASE statement? 'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth – wafw1971 Feb 08 '13 at 10:42

Adding a Month Name Sort within T-SQL Code

2 Answers2