I'm having an interesting challenge. After having spent several hours looking for a solution using Google, I thought it'd be better to go straight to the SQL-gurus right away. 🙂
The thing is that I have this table field that is populated with a string value, which should be a valid date. For example, 13th July, 2008 would be entered like 13-07-08. I cannot change this (it's been hard coded in another programme), so I'll have to make the best of it.
To be able to work with it I'm converting it to datetime format using CAST. This works, as long as there is valid 'date' in that field. Oh, I should mention that if there's no date set, the value could be either 00-00-00 or 99-99-99. In that case, I'm replacing the original date with NULL.
The problem is that people can enter any value into this field manually. So for example, they could enter 00-00-0 (forgetting the last 0). Or they could type something else that cannot be converted, like 00-00-08. Or who knows what somebody will come up with tomorrow. :w00t:
Anyway, what I'd really like is using a query that catches such conversion problems. Instead of quitting processing the query completely, it's fine if problematic 'dates' are replaced with NULL. Is that possible?
For your reference, here's the query I'm using right now:
(SELECT CASE orgvalue
WHEN '99-99-99' THEN NULL
WHEN '00-00-00' THEN NULL
CAST(('20' + RIGHT(orgvalue,2) + '-' +
(select case SUBSTRING(orgvalue,CHARINDEX('-',orgvalue)+2,1)
when '-' then '0' + SUBSTRING(orgvalue,CHARINDEX('-',orgvalue)+1,1)
END) + '-' +
(select case SUBSTRING(orgvalue,2,1)
when '-' then '0' + LEFT(orgvalue,1) + 'T00:00:00'
else LEFT(orgvalue,2) + 'T00:00:00'
end)) as datetime)
END) AS realDateTime