tags:

views:

70

answers:

7

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]

+3  A: 

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
dcp
+1  A: 

In Oracle we use DECODE. But I think in SQL Server you have to go with CASE. Example:

SELECT CASE WHEN name = 'March' THEN '3' 
            WHEN name = 'April' THEN '4' 
            ELSE 'something else' 
       END
FROM months
Jürgen Hollfelder
you can also use CASE in Oracle, its more readable .
mcha
I can agree with that. CASE is more readable than DECODE. Actually the very best would be a JOIN or SUB-SELECT with a "lookup-table". That prevents hardcoding. But in specific case it might even be a data formatting issue as I judge from some other up voted answers.
Jürgen Hollfelder
+2  A: 

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
Tomalak
PS: This is standard SQL systax that will work across different DB engines.
Tomalak
+2  A: 

If you're really using SQL server, you could try the following

SELECT TOP 1 MONTH(CAST('01 ' + name + ' 2000' AS DATETIME))
FROM months

But as others have said, your use of the LIMIT keyword suggests you might be on another RDBMS

Ed Harper
Also, for merely twelve predictable values, building a string, casting it to `DATETIME` and then passing it through `MONTH()` will most definitely be slower than `CASE WHEN`, and it will blow up when there is a month with a typo in it.
Tomalak
A: 

I think the best you'll be able to do is a CASE statement.

select case name
           when 'January' then 1
           when 'February' then 2
           ...
           when 'December' then 12
       end as MonthNumber
   from months
Joe Stefanelli
+1  A: 

Just for variety

SELECT 
CEILING(CHARINDEX(name,'January   February  March     April     May       June      July      August    September October   November  December'
COLLATE sql_latin1_general_cp1_ci_as )/10.0) 
 month_num
 FROM months
Martin Smith
@Martin, might want to add LOWER in front of both name and the searchstring - Nick's original question was based on the value 'march'.
Mark Bannister
@Mark - I've lobbed a `COLLATE` in there to deal with that.
Martin Smith
+1 for originality
Heinzi
+1  A: 

Try using SQLServer's date conversion functions, like so:

select TOP 1 datepart(month,convert(datetime, left(name,3) + ' 01, 01', 107))
FROM months
Mark Bannister