So, while I recognize that date formatting etc. should be done in the presentation layer, I am interested to know if anyone has seen or recognized this difference (please try at home, if so inclined) I am a little baffled and mostly curious, the sample code first.
UPDATE: To clarify based on the initial responses, I am aware the date IS invalid or better "not safe", since the particular field that I am more generally concerned about comes from user input." That is, while I am aware that validation/formatting aren't SQL 2008 strong suits, it is at least curious to me that DATETIME is more forgiving and I am wondering as to cause to see how forgiving."
DECLARE @RawValue NVARCHAR(30), @Value DATETIME;
SET @RawValue = '01/20.1901'
SET @Value = CAST(@RawValue AS DATETIME)
PRINT @Value
This produces the correct result for my server settings: Jan 20 1901 12:00AM
However if the penultimate line is changed to (replacing DATETIME with DATE):
SET @Value = CAST(@RawValue AS DATE)
Msg 241, Level 16, State 1, Line 8 Conversion failed when converting date and/or time from character string.
Is there an explanation out there? To be clear it doesn't matter if I DECLARE @Value to be a DATE or DATETIME or even an NVARCHAR -- Same result. The error message seems to suggest that it is having trouble converting the date AND/OR time, why would DATETIME behave any differently?
Thanks,