How to 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

CustomText1contains month data with 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 the CASE statement. Check the online document , you have several ways to do this, this is the easiest way to repeat all the other 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