January 10, 2011 at 12:07 pm
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.
Jamie
January 10, 2011 at 12:08 pm
What compatibility level are you using in those databases?
January 10, 2011 at 12:10 pm
Compat Level - 10.. but databases were written in 8 or 9 - but you'll have to wait if you want an example... snowed here today and they shut us down.
Jamie
January 10, 2011 at 12:19 pm
Yes, I would like more details when you have a chance. In the meantime, enjoy your snow day and have a safe trip home.
January 25, 2011 at 9:21 am
However, when I run the command DECLARE @myDate DATETIME2
SET @myDate = '1752-09-09'
PRINT @myDate in SQL Server 2008 R2, I do actually get results:
1752-09-09 00:00:00.0000000
January 25, 2011 at 10:54 am
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?
Jamie
January 26, 2011 at 8:40 am
Executed without any problem as it was expected :unsure::Whistling:
Cannot add screenshot 🙁
What's the trick - missed point 🙂
February 4, 2011 at 9:51 am
I got this one right but only because I could tell the surprise in the author's statement that this works better than datetime. I learned something here but I agree with everyone else that they should have stuck to actual output choices for the answers instead of what someone may or may not expect. I actually said out loud, "oh I bet this ticked some people off!" before answering.
February 4, 2011 at 10:08 am
gchornenkyy (1/26/2011)
What's the trick - missed point 🙂
The trick is that date doesn't actually exist in some countries, including the U.S.
February 4, 2011 at 10:12 am
Brandie Tarvin (2/4/2011)
The trick is that date doesn't actually exist in some countries, including the U.S.
But given that the date does exist in many other countries, SQLServer is completely right it allowing it. So the assertion in the 'correct' answer that you would expect it to fail is not true.
February 4, 2011 at 10:55 am
the 'correct' answer that you would expect it to fail is not true.
I agree, the 'CORRECT' answer is that we would hope that SQL Server has not put out a buggy piece of code and for us to expect it to fail would be counterintuitive. To the contrary, we (the users) expect it to work. The Correct Answer - it works as expected.
Jamie
February 4, 2011 at 10:56 am
Toreador (2/4/2011)
Brandie Tarvin (2/4/2011)
The trick is that date doesn't actually exist in some countries, including the U.S.
But given that the date does exist in many other countries, SQLServer is completely right it allowing it. So the assertion in the 'correct' answer that you would expect it to fail is not true.
I'm just answering the previous poster's question, that's all. He seems to have missed the entire conversation in the previous 5 pages.
February 4, 2011 at 2:05 pm
Brandie Tarvin (2/4/2011)
Toreador (2/4/2011)
Brandie Tarvin (2/4/2011)
The trick is that date doesn't actually exist in some countries, including the U.S.
But given that the date does exist in many other countries, SQLServer is completely right it allowing it. So the assertion in the 'correct' answer that you would expect it to fail is not true.
I'm just answering the previous poster's question, that's all. He seems to have missed the entire conversation in the previous 5 pages.
As for me I provided correct answer. The problem it that the question was not properly formalized - condition "... if you execute it in US" missed 😉
Have no any problem wit that date in Canada 🙂
Regards
February 14, 2011 at 7:02 am
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?
February 15, 2011 at 9:43 am
Here is a better example:
Jamie
Viewing 15 posts - 46 through 60 (of 81 total)
You must be logged in to reply to this topic. Login to reply