Sql: Extract Date From String

There is a text field called myDate. This field can contain either 1) 'Fiscal year ending someDate' or 2) 'dateA to 'dateB'. In situation 1), I want to set the field date1 = to som

Solution 1:

This doesn't look complicated enough to need a "proper" regular expression. Those textual dates can be parsed directly into a DATETIME type by SQL without any mucking around, as you can see by running this query:

SELECTCAST('1/1/2000'AS DATETIME), CAST('January 1, 2000'AS DATETIME), CAST('Jan 1, 2000'AS DATETIME)

To get -1 year and +1 day, just use DATEADD, e.g.

SELECT DATEADD(dd, 1, DATEADD(yy, -1, 'January 1 2000'))

...so, all you really need to do is cope with your two different cases and grab the dates out. So, something like:

SELECTCASEWHEN myDate LIKE'fiscal year ending %'THENCAST(DATEADD(dd, 1, DATEADD(yy, -1, REPLACE(myDate, 'fiscal year ending ', ''))) AS DATETIME) 
    ELSECAST(LEFT(myDate, PATINDEX('% to %', myDate)) AS DATETIME)
  END'FromDate',
  CASEWHEN myDate LIKE'fiscal year ending %'THENCAST(REPLACE(myDate, 'fiscal year ending ', '') AS DATETIME)
    ELSECAST(SUBSTRING(myDate, PATINDEX('% to %', myDate) +4, 100) AS DATETIME)
  END'ToDate'FROM 
  ...whatever

...should do the trick. I've not really tested that, but hopefully it'll give you enough of an idea to see what I'm getting at.

Note that some of the results will probably depend on the language settings of your server/database. For example, while 1/1/2000 is always going to be 1 Jan, is 3/4/2000 the third of April, or the fourth of March?