CODEWITHSUNDEEP
sqlsql-server
Niki
Niki

Reputation: 1924

Convert result within statement

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]

Upvotes: 6

Views: 4129

Answers (7)

Martin Smith
Martin Smith

Reputation: 453887

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

Upvotes: 1

user359040
user359040

Reputation:

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

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135918

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

Upvotes: 0

Ed Harper
Ed Harper

Reputation: 21505

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

Upvotes: 2

Tomalak
Tomalak

Reputation: 338396

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

Upvotes: 3

hol
hol

Reputation: 8423

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

Upvotes: 1

dcp
dcp

Reputation: 55467

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

Upvotes: 3

Related Questions