I'm converting varchar to datetime in Sql Server 2005. Can I force Sql Server to fail if provided varchar has unexpected format?
Example:
select convert(datetime, '01-2010-02', 103)
Expected result: query fails because 103 means dd/mm/yyyy (see msdn).
Actual result: 2010-02-01 00:00:00.000
Main purpose of requested enforcement is order of day and month. If varchar is provided in format yyyy-mm-dd then Sql Server will treat mm as day and dd as month because of day/month order in provided format (dd/mm/yyyy).
Note: I can write custom function to manually handle this case. But I hope such enterprise DB already can work strictly with data.
I am afraid you have to use CLR Function and take advantage of using DateTime.TryParseExact method. Not an elegant solution but could work.