Geek Answers Handbook

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

So, although I know that date formatting, etc. should be done at presentation level, I am interested to know if anyone saw or recognized this difference (please try it at home, if so) I am a little puzzled and mostly curious, first a code example.

UPDATE. To clarify based on the original answers, I know that the IS date is invalid or better “unsafe”, since the specific field in which I am most concerned comes from user input. ”That is, while I check / format are not strong suits SQL 2008, I'm at least curious that DATETIME is more forgiving, and I'm wondering how to get how to forgive .

DECLARE @RawValue NVARCHAR(30), @Value DATETIME;
SET @RawValue = '01/20.1901'

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

This gives the correct result for my server settings: January 20, 1901 12:00

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 error while converting date and / or time from a character string.

Is there any explanation? To be clear, it doesn't matter if I DECLARE @Value be DATE or DATETIME or even NVARCHAR - the same result. The error message seems to be having problems converting the AND / OR date , why does DATETIME behave differently?

Thanks,

+4
source share
3 answers

, DATE DATETIME - . DATE - DATETIME . , CAST ('17520910' AS DATE) , DATETIME . , , , 2 14 . DATETIME, DATE 1 .

. D - , D + 3 - . D - DATE, D + 3 .

, , DATE, Microsoft .

+5

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

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

SET @RawValue = '01/20/1901'


SET @RawValue = '01.20.1901'


SET @RawValue = '01/20.1901'

SQL Server Date

0
source

More articles:


All Articles