Search code examples
sqldatabasedatesybase

Converting DD MMM YYYY to a date field with SQL

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

  • My solution:

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