CODEWITHSUNDEEP
sqldatedatetimesql-server-2008-r2
Ahmad Ragab
Ahmad Ragab

Reputation: 1117

DATE vs. DATETIME casting of invalid dates in SQL SERVER 2008 R2

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,

Upvotes: 6

Views: 23309

Answers (3)

dbuskirk
dbuskirk

Reputation: 526

It is worth mentioning that DATE and DATETIME are completely different datatypes. DATE is not simply DATETIME with the time removed. For example, CAST('17520910' AS DATE) works, while the similar conversion to DATETIME does not. For you history buffs, there was no such day in England or her colonies, the calendar skipped from September 2nd to September 14. Unlike DATETIME, DATE goes back to the year 1 without considering calendar system.

Another important difference is the lack of implicit type conversion to add a number of days directly to a date. If D is datetime, D+3 is the date three days hence. If D is DATE, then D+3 produces an error.

I am assuming that since new code for implicit conversion was created from scratch for DATE that Microsoft simply made it a tad more fastidious.

Upvotes: 7

Jade
Jade

Reputation: 2992

your format is incorrect

use

SET @RawValue = '01/20/1901'

or 

SET @RawValue = '01.20.1901'

instead of

SET @RawValue = '01/20.1901'

The problem is in SQL Server engine for parsing the datetime and cast it to Date

Upvotes: 0

Naresh Pansuriya
Naresh Pansuriya

Reputation: 2045

can you tried with 

DECLARE @RawValue NVARCHAR(30), @Value DATE;
SET @RawValue = '01.20.1901' -- or '01/20/1901'

SET @Value = CAST(@RawValue AS DATE)
PRINT @Value

Upvotes: 0

Related Questions