SQL Varchar convert to Date - Sybase

I am using Sybase IQ and have the following stored as a varchar:

01October 2010

I want to convert this from varchar to date datatype with the following format:

yyyy-mm-dd   eg.2010-10-01

How would I write this SQL statement? Thanks in advance.

Solution

With difficulty. There's a reason you should never store dates and times as strings.

It's been awhile since I've used Sybase, but what we need to do is get the field into YYYY-MM-DD format, and then pass it to the DATE() or DATETIME() function.

Let's assume the first two characters are always the day of the month, and the last 4 characters are the year. That means everything in between is the month. Let's also assume that there are no leading or trailing spaces. If either of these assumptions fails, then the query will fail.

Then you can do something like this:

SELECT DATE (
        RIGHT(UnnamedField,4) + '-' +
        CASE LTRIM(RTRIM(SUBSTRING(Unnamed,3,LEN(Unnamed) - 6)))
            WHEN 'January' THEN '01'
            WHEN 'February' THEN '02'
            WHEN 'March' THEN '03'
            .
            .
            .
            WHEN 'December' THEN '12'
        END + '-' + LEFT(UnnamedField,2)
    )
FROM UnnamedTable

Note that, as others have mentioned, the date data type is not a formatted datatype. If possible you should format it in your application. If you must do it in the query, use the CONVERT() function.