Converting DD MMM YYYY to a date field with SQL

Question

I have a string value E.g 28Dec2013 which I need to convert to a date field in SQL.

I need to convert to a date field so I can carry out calculations on the field. For example if I query: select max(date) it returns the value of '31Oct2015' - However I know this value should be '01Jan2016 '

Does anyone have any ideas how to go about this.

I'm unsure because of the format of the string how to go about this. I am running this on Sybase.

Thanks a lot.

Solution 1

My solution:

SELECT CONVERT(DATE, SUBSTRING(thedate, 0, 2) || ' ' || SUBSTRING(thedate, 3, 3) || ' ' || SUBSTRING(thedate, 6, 4), 106) FROM calendartable; 

Solution 2

Since I don't know Sybase I'd go with a more or less dbms independent solution:

SUBSTRING(col FROM 6 for 4) || CASE SUBSTRING(col from 3 for 3)
                               when 'Jan' then '01'
                               when 'Feb' then '02'
                               when 'Mar' then '03'
                               when 'Apr' then '04'
                               when 'May' then '05'
                               when 'Jun' then '06'
                               when 'Jul' then '07'
                               when 'Aug' then '08'
                               when 'Sep' then '09'
                               when 'Oct' then '10'
                               when 'Nov' then '11'
                               when 'Dec' then '12' end
                            || SUBSTRING(col from 1 for 2)

ANSI SQL compliant!

Solution 3

SELECT CONVERT(VARCHAR(9),thedate,106) FROM calendartable

Use convert.

Value 106 is dd/mon/yy

Solution 4

Use Simple CAST function:

SELECT CAST('28Dec2013' AS DATE)

Query for Table:

  SELECT CAST(Column AS DATE) FROM YourTable

View more solutions

Comments

[rkyyk]

Hi, thanks for the idea, but I had tried this and have just tried running your above query and receive the 'data type conversion is not possible' error

[rkyyk]

Hi, again tried this idea and again receive the 'data type conversion is not possible. CONVERT argument does not match the required data type'

[Matt]

did you change datefield to your field name that contains the date

[rkyyk]

Hi Matt, yep ran this query - " SELECT CONVERT(VARCHAR(24),THEDATE,106) FROM CALENDARTABLE "

[rkyyk]

Hi, thanks this gave me an idea. I've posted my solution below :)

[rkyyk]

Hi Matt, thanks for the help, I found a work around myself, see answer above :)

[Veera]

Do you know any online editor like sqlfiddler for sybase

[rkyyk]

Hi, I'm sorry I'm afraid I don't, I've just been working on a development database