Jamie Longstreet-481950 (1/25/2011)
What happens when:Declare @dt datetime2
SET @dt='1752-09-09'
DECLARE @dt2 datetime
DECLARE @dt3 varbinary
SET @dt3=CONVERT(VARBINARY,@dt)
SELECT @dt3
SELECT cast(CAST(@dt3 AS NVARCHAR) AS varbinary)
SELECT CONVERT(DATETIME2,cast(CAST(@dt3 AS NVARCHAR) AS varbinary))
SET @dt2=@dt
SELECT @dt
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
Conversion failed when converting date and/or time from character string.
It is important that when you upgrade from Ver 90 SQL to Ver 100 that you explicitly declare previous datetime data to prevent the accidental assignment of a longer type to a shorter type.
Also, note above... how does one convert to a numeric value?
Jaime, I'm not sure I understand the above in reference to the below:
You're getting errors converting from Varbinary and Nvarchar to a datetime2, but what has that got to do with your original post about GetDate() defaulting to Datetime2 and having problems converting to datetime?
Jamie Longstreet-481950 (1/25/2011)
Something else is a bit disturbing here... the datetime2 is often a default time now for getdate() (or seems to be) and it can be problematic in databases created prior to the new variable type as the prefix ".000000" on the end cannot be read and generates an error. I have had to write workarounds more than once in the database.
I've tried and tried to understand the connection between the two and I'm missing something. The following code doesn't cause me any conversion issues. SELECT GetDate(), Convert(datetime2,GetDate()), Convert(datetime,GetDate()).
What am I missing that you're having problems with?