Flip month and day in SQL Server DateTime field

I have a portion of my data that has been parsed incorrectly (due to earlier mistakes in handling Culture) so that the month and the day should be flipped. Is there an easy way to do this in SQL Server? Fortunately, it is still early enough in the dataset that it is easy to locate the bad data.

Update I think this will work:

SELECT seen, DATEADD(DAY, DATEDIFF(DAY, seen,
    CONVERT(DATETIME, CAST(YEAR(seen) AS VARCHAR(4)) 
        + RIGHT('0'+CAST(DAY(seen) AS VARCHAR(2)),2) + 
        + RIGHT('0'+CAST(MONTH(seen) AS VARCHAR(2)),2), 112)), seen)
FROM TermStats
WHERE seen < '2011-09-01' AND DAY(seen) <= 12

But I think I can do better. All good dates are after 9/1. (You can tell I really lucked out here... lol)

SELECT DISTINCT YEAR(seen), MONTH(seen), DAY(seen)
FROM TermStats
ORDER BY YEAR(seen), MONTH(seen), DAY(seen)

2006    2   13
2011    3   9
2011    4   9
2011    5   9
2011    6   9
2011    9   3
2011    9开发者_如何学Go   4
2011    9   5
2011    9   6

Personally I wouldn't do it through string manipulation. It's entirely possible that you can do it nicely in a stored procedure, but I'd personally (as someone with little SQL experience) write a client side tool to fix it:

• Fetch the ID and date of every row you need to fix (fetching the date/time value as a date/time value, not as a string)
• Create the correct date, e.g. in C# using new DateTime(dt.Year, dt.Day, dt.Month); to flip the fields of the wrong date
• Update the database with a parameterized query - again, not converting the dates into strings

Basically, wherever you can, avoid conversion between text and other formats. It only leads to the kind of pain you've already discovered.