SQL Server : split string in SELECT statement

Question

I want to split date in column in 3 fields, I use this query

SELECT  
SUBSTRING(Account.date, 1, 2) AS "Month",
SUBSTRING(Account.date, 4, 2) AS "Day",
SUBSTRING(Account.date, 7, 4) AS "Year"
FROM Account

Almost all data is in format 02/11/2000, but some of it can be 02/November/2000 or 2/11/2000.

Only common thing is that data separated by /. How can I separate this column using the delimiter?

Answer 1

Surprisingly CAST('2/November/2000' as datetime) works (checked on SQL Server 2008), gives value 2000-11-02 00:00:00.000

SELECT  
Month(CAST(Account.date AS DateTime)) "Month",
Day(CAST(Account.date AS DateTime)) "Day",
Year(CAST(Account.date AS DateTime)) "Year",

FROM Account

But as rightly pointed out in comment how do you know if "02/11/2000" is November 2, 2000 or February 11, 2000?

Also the spelling of Month names must be absolutely correct else conversion fails. Since you are storing dates as string there is chance that entry like November , Agust etc could have been made .

You should never store date values as strings.