Post reply

INSERTing datetimeoffsets

  • November 1, 2016 at 10:56 pm

    #77309

    Comments posted to this topic are about the item INSERTing datetimeoffsets

  • November 2, 2016 at 12:36 am

    #1910041

    DOH! It was INvalid! Time for coffee...

    Just because you're right doesn't mean everybody else is wrong.

  • November 2, 2016 at 12:58 am

    #1910043

    Nice question, technically both:

    1. INSERT dbo.TimeZoneOffsetTest (mytime) CAST(SELECT '20161015 08:15 -4:25' as DATETIMEOFFSET)

    and

    2. INSERT dbo.TimeZoneOffsetTest (mytime) SELECT '20161015 08:15 2:00'

    are invalid, option 1 contains an invalid sub-query SELECT. Remove the SELECT and it will be fine.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • November 2, 2016 at 2:21 am

    #1910048

    Henrico Bekker (11/2/2016)

    Nice question, technically both:

    1. INSERT dbo.TimeZoneOffsetTest (mytime) CAST(SELECT '20161015 08:15 -4:25' as DATETIMEOFFSET)

    and

    2. INSERT dbo.TimeZoneOffsetTest (mytime) SELECT '20161015 08:15 2:00'

    are invalid, option 1 contains an invalid sub-query SELECT. Remove the SELECT and it will be fine.

    Removing the SELECT still doesn't work. Both queries don't execute.

    Igor Micev,My blog: www.igormicev.com

  • November 2, 2016 at 4:25 am

    #1910054

    (1) and (2) were both invalid.

  • November 2, 2016 at 5:20 am

    #1910062

    Both 1 and 2 were invalid, but 2 was more seriously invalid than 1, so I guessed right. For once! 😀

  • November 2, 2016 at 5:27 am

    #1910063

    edwardwill (11/2/2016)

    Both 1 and 2 were invalid, but 2 was more seriously invalid than 1, so I guessed right. For once! 😀

    Yup, both 1 and 2 were invalid, but my guess was the wrong one. Oh well.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 2, 2016 at 6:02 am

    #1910071

    As noted, both 1 & 2 appear to be invalid

    INSERT dbo.TimeZoneOffsetTest (mytime) SELECT CAST('20161015 08:15 -4:25' as DATETIMEOFFSET)

    go

    INSERT dbo.TimeZoneOffsetTest (mytime) SELECT '20161015 08:15 +2:00'

    go

    will work

  • November 2, 2016 at 7:11 am

    #1910087

    Rune Bivrin (11/2/2016)

    DOH! It was INvalid! Time for coffee...

    Same here.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 2, 2016 at 7:38 am

    #1910096

    I'm confused.

    The correct answer is given as:

    INSERT dbo.TimeZoneOffsetTest (mytime) SELECT '20161015 08:15 2:00'

    The explanation is:

    Of these, the this item is invalid.

    INSERT dbo.TimeZoneOffsetTest (mytime) CAST(SELECT '20161015 08:15 -4:25' as DATETIMEOFFSET)

    SQL Server needs to have a positive or negative value for the time offset. A value is invalid if it's just a number.

    I think both 1 and 2 are invalid aren't they (I chose 1)?

  • November 2, 2016 at 8:56 am

    #1910140

    I think both these insert statements are failing.

    INSERT dbo.TimeZoneOffsetTest (mytime) CAST(SELECT '20161015 08:15 -4:25' as DATETIMEOFFSET)

    INSERT dbo.TimeZoneOffsetTest (mytime) SELECT '20161015 08:15 2:00'

    Thanks.

  • November 2, 2016 at 9:58 am

    #1910157

    Expected 1 and 2 ; went for 2 so guessed wrong

    Now run this through, as with others, both 1 and 2 fail

    Can't see this been SQL version specific or down to settings

    Am I missing something here?

    Thanks

    - Damian

  • November 2, 2016 at 10:17 am

    #1910162

    Igor Micev (11/2/2016)

    Henrico Bekker (11/2/2016)

    Nice question, technically both:

    1. INSERT dbo.TimeZoneOffsetTest (mytime) CAST(SELECT '20161015 08:15 -4:25' as DATETIMEOFFSET)

    and

    2. INSERT dbo.TimeZoneOffsetTest (mytime) SELECT '20161015 08:15 2:00'

    are invalid, option 1 contains an invalid sub-query SELECT. Remove the SELECT and it will be fine.

    Removing the SELECT still doesn't work. Both queries don't execute.

    This is most likely a copy/paste error by Steve. If you put the SELECT command before the CAST in #1 it works.

    INSERT dbo.TimeZoneOffsetTest (mytime) SELECT CAST('20161015 08:15 -4:25' as DATETIMEOFFSET)

    INSERT dbo.TimeZoneOffsetTest (mytime) SELECT '20161015 08:15 2:00'

    J DBA

  • November 2, 2016 at 10:25 am

    #1910168

    Despite of the confusion, it's a good question.

  • November 2, 2016 at 10:38 am

    #1910171

    Hmmm. I guessed #1 and got it wrong, even though the description of the line that errors is the sql from #1. I just ran the code and it looks like both #1 and #2 fail. Looks like I just chose the wrong right answer #1 instead of the right right answer #2.

Viewing 15 posts - 1 through 15 (of 27 total)

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