How do I change text data to numeric in SQL Server?

Question

SELECT 
    a.AccountNumber, a.FirstName, a.LastName, a.Address, a.City, a.State, 
    a.Zip, a.EmailAddress, a.PhoneNumber, a.LastUpdated, a.LastVisit, 
    a.TotalSales, a.AccountOpened, a.CustomText4 as StoreCode, 
    CASE
        WHEN (a.CustomText1 IS 'JAN') THEN '1' 
    END AS DOB, 
    GETDATE() as Extract_date
FROM 
    database.dbo.Customer a

CustomText1 column has Month data with a text data.I am trying to convert JAN-DEC to Numeric.

CASE WHEN IS '' isn't working.

Answer 1

"IS" is not a valid expression for CASE statement. Check the online doc, you have a couple ways to do it, this is the simplest way, repeat for the rest of the months.

SELECT DOB = 
    CASE a.CustomText1
        WHEN 'JAN' THEN '1'
        WHEN 'FEB' THEN '2'
        WHEN 'MAR' THEN '3'
        ELSE a.CustomText1
    END
FROM database.dbo.Customer a

Answer 2

Greg's answer works, but it's over 12 lines. Here's how to do it in one.

SELECT MONTH(CAST('01' + CustomText1 + '00' AS DATE))
FROM dbo.Customer