Fun(?) with DATETIME2

  • 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

  • What compatibility level are you using in those databases?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • Yes, I would like more details when you have a chance. In the meantime, enjoy your snow day and have a safe trip home.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • 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

  • Executed without any problem as it was expected :unsure::Whistling:

    Cannot add screenshot 🙁

    What's the trick - missed point 🙂

  • 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.

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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.

  • 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

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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

  • 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?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 15 posts - 46 through 60 (of 81 total)

You must be logged in to reply to this topic. Login to reply