title: DATE vs. DATETIME casting of invalid dates in SQL SERVER 2008 R2
body: <p>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. </p>
  
  <p><em>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 <strong>how forgiving</strong>."</em></p>
  
  <pre><code>DECLARE @RawValue NVARCHAR(30), @Value DATETIME;
  SET @RawValue = '01/20.1901'
  
  SET @Value = CAST(@RawValue AS DATETIME)
  PRINT @Value
  </code></pre>
  
  <p>This produces the correct result for my server settings: <strong>Jan 20 1901 12:00AM</strong></p>
  
  <p>However if the penultimate line is changed to (replacing DATETIME with DATE):</p>
  
  <pre><code>  SET @Value = CAST(@RawValue AS DATE)
  </code></pre>
  
  <p><em>Msg 241, Level 16, State 1, Line 8
  Conversion failed when converting date and/or time from character string.</em></p>
  
  <p>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 <strong>AND/OR</strong> time, why would DATETIME behave any differently?</p>
  
  <p>Thanks,</p>