SQL: extract date from string

Question

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 someDate.

In situation 2), I want to set the field date1 = dateA and the field date2 = dateB.

All the dates (someDate, dateA, dateB) can be written as 1/1/2000, Jan 1, 2000, or January 1, 2000.

How do I go about extracting the dates from myDate and inserting them into the correct fields?

Answer 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:

SELECT CAST('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:

SELECT
  CASE 
    WHEN myDate LIKE 'fiscal year ending %' THEN CAST(DATEADD(dd, 1, DATEADD(yy, -1, REPLACE(myDate, 'fiscal year ending ', ''))) AS DATETIME) 
    ELSE CAST(LEFT(myDate, PATINDEX('% to %', myDate)) AS DATETIME)
  END 'FromDate',
  CASE
    WHEN myDate LIKE 'fiscal year ending %' THEN CAST(REPLACE(myDate, 'fiscal year ending ', '') AS DATETIME)
    ELSE CAST(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?