How to get year-date in sequence. It generates random in SSMS

Question

Below is the sql query I have wrote to populate year- month but it generates random. I want it to be like Jan -15, feb -15 ........dec-15, jan-16............dec-16....

SELECT
  CASE WHEN dbo.X_HIST_VASCULAR.MONTH = 'January' then 'Dec ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2001)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'February' then 'Jan ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'March' then 'Feb ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'April' then 'Mar ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'May' then 'Apr ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'June' then 'May ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'July' then 'June ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'August' then 'July ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'September' then 'Aug ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'October' then 'Sep ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'November' then 'Oct ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
WHEN dbo.X_HIST_VASCULAR.MONTH = 'December' then 'Nov ' + convert(char(2),dbo.X_HIST_VASCULAR.YEAR-2000)
END
FROM
  dbo.X_HIST_VASCULAR

So. If i query Month select distinct month from x_hist_vascular This is the results i get month February June August April May December January September October July November March

If i do Asc or Desc it goes throw alpha order. Like for Asc it display April first and for Desc it displays September. I want months to be displayed in calendar format.

"Generates random"? What do you mean, there's nothing that would generate a random value in that query. What are you actually trying to do here. Sample data and expected results are going to be needed here I think, — Larnu
Does dbo.X_HIST_VASCULAR have a DateTime column with the date in? — Andrew Morton
It looks like it should work assuming the data is correct. What do yo mean it "generates random" - Show some sample data. — John Bustos
@Larnu and John Bustos I suspect it means the data is returned in an apparently random order. Gee351, would you confirm if that is the case, please? — Andrew Morton
Do you have an actual date column you can order by instead of the string representation of the month name? — Tab Alleman

sticky bit sticky bit · Accepted Answer · 2018-06-01T17:10:43

If there is no columns with an appropriate data type to order by, which I suspect, you can add the following at the end of your query to sort by an numerical value for each month:

ORDER BY
CASE
WHEN dbo.X_HIST_VASCULAR.MONTH = 'January' then 12
WHEN dbo.X_HIST_VASCULAR.MONTH = 'February' then 1
...
WHEN dbo.X_HIST_VASCULAR.MONTH = 'November' then 10
WHEN dbo.X_HIST_VASCULAR.MONTH = 'December' then 11
END

Note, that the months are off by minus one in your query ('January' then 'Dec ', etc.). I did that here too, in the believe that this is intentional. If That was a mistake, you have to correct it in both places.

The best however would be, to revise the design and only use a column with a proper date/date time data type. That would make things a lot easier.

How to get year-date in sequence. It generates random in SSMS

1 Answers