Convert result within statement

Question

Let's say I have the following simple query

SELECT TOP 1 name FROM months

which returns name = "march". Is it possible to convert this result? Instead of "march" I want name = "3". Is SQL capable of doing such things? I'm using a MSSQL database.

[update] Corrected the query. While writing this simple example I mixed it up with MySQL [/update]

Answer 1

If you want to map a fixed set of input values against a fixed set of output values, CASE WHEN is your friend:

SELECT
  CASE name
    WHEN 'january'  THEN 1
    WHEN 'february' THEN 2
    WHEN 'march'    THEN 3
    /* ... */
  END as num
FROM
  months

Answer 2

If you just want month number, you could do this:

SELECT
   CASE
      WHEN name = 'January' then 1
      WHEN name = 'February' then 2
      WHEN name = 'March' then 3
      WHEN name = 'April' then 4
      WHEN name = 'May' then 5
      WHEN name = 'June' then 6
      WHEN name = 'July' then 7
      WHEN name = 'August' then 8
      WHEN name = 'September' then 9
      WHEN name = 'October' then 10
      WHEN name = 'November' then 11
      WHEN name = 'December' then 12
   END month_num
FROM months