Weird datetime conversions

  • anders-731262

    Ten Centuries

    Points: 1256

    Comments posted to this topic are about the item Weird datetime conversions

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    The given code gives error that is NOT the option in the provided options...

    The Error is :

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '<'.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • RBarryYoung

    SSC Guru

    Points: 143327

    I couldn't really read this question, It was all just IFCodes.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    The question looks like this (at least on my PC)...

    SELECT CONVERT(datetime,-1)

    UNION

    SELECT CONVERT(datetime,0)+CONVERT(datetime,-1)

    UNION

    SELECT CONVERT(datetime,-1)-CONVERT(datetime,0)

    UNION

    SELECT CONVERT(datetime,0)-CONVERT(datetime,1)

    UNION

    SELECT CONVERT(datetime,0)-1

    Am I about to learn learn something new here, or is it a formating issue...?

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • Douglas Duncan

    SSC Eights!

    Points: 897

    Atif Sheikh (5/6/2009)


    The question looks like this (at least on my PC)...

    SELECT CONVERT(datetime,-1)

    UNION

    SELECT CONVERT(datetime,0)+CONVERT(datetime,-1)

    UNION

    SELECT CONVERT(datetime,-1)-CONVERT(datetime,0)

    UNION

    SELECT CONVERT(datetime,0)-CONVERT(datetime,1)

    UNION

    SELECT CONVERT(datetime,0)-1

    Am I about to learn learn something new here, or is it a formating issue...?

    Hey Atif, it is a formatting issue. All of the <span class=...> text is HTML markup that was not being properly rendered. I had to copy and paste into a text editor and remove the mark up just to figure out what the code was doing. Hopefully someone comes by soon to resolve the issue, or one could technically say that running the code results in an error. 😉

  • mverma4you

    Ten Centuries

    Points: 1013

    your question may lead confusion as you are usign third party tool to write the query. your TSQL include code inside the TSQL statement.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    RBarryYoung (5/6/2009)


    I couldn't really read this question, It was all just IFCodes.

    The code was properly formatted in the daily mail, but not on the site. For those who have lost their mail, or who don't subscribe to it, here is the code as I found it in my mail:

    SELECT CONVERT(datetime,-1)

    UNION

    SELECT CONVERT(datetime,0)+CONVERT(datetime,-1)

    UNION

    SELECT CONVERT(datetime,-1)-CONVERT(datetime,0)

    UNION

    SELECT CONVERT(datetime,0)-CONVERT(datetime,1)

    UNION

    SELECT CONVERT(datetime,0)-1


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 8895

    Apart from the formatting issue, what does it really mean if one adds two dates together? Yeah, I can see that adding today's date and tomorrow's date as in

    SELECT GETDATE()+GETDATE()+1

    yields

    2118-09-12 20:25:12.580

    as off the moment I ran the query, since it's just treated like a floating point addition that then gets converted back to a datetime, but does it really make sense to attempt such a thing in the first place?

    Anyhow, I got the answer wrong, but did learn something new since I was under the impression that converting a negative integer to a datetime would cause some kind of conversion error.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • anders-731262

    Ten Centuries

    Points: 1256

    mverma4you (5/6/2009)


    your question may lead confusion as you are usign third party tool to write the query. your TSQL include code inside the TSQL statement.

    Hi,

    i cant tell you what went wrong with the formatting. This question was written using the inline editor in the contribution center.

    Its too bad, since it more or less ruined the question.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Jan Van der Eecken (5/7/2009)


    Apart from the formatting issue, what does it really mean if one adds two dates together?

    Nothing. The explanation is in fact misleading, since that is not what is happening here.

    If you check the "addiciton" topic in Books Online, you'll see that it mainly describes addition of two numbers, but also mentions the possibility of adding a number (in days) to a date. So this means that there are two alllowed forms of addition:

    1. number + number

    2. datetime + number

    The form used in this QotD: datetime + datetime is not supported - but it can be made into a supported form by first implictly converting the second datetime expressions to a numeric expression.

    Note that this is just an explanation and in no way an attempt to condone this behaviour. Even though the datetime + number form of addition is documented, I would never use it in production code as I consider it a gruesome hack. Please, everybody, use DATEADD(day, number, datetime) instead.

    Oh, and I also never rely on the current behaviour of implicit conversion between datetimes and numerics. (shudder)

    I was under the impression that converting a negative integer to a datetime would cause some kind of conversion error.

    If will if you use smalldatetime instead of datetime... :w00t:


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 8895

    I agree completely with you, Hugo. I hate implicit conversions because they lead to all kinds of problems while your code is in production. Conversions from varchars to datetimes to numericals and back or in any random order are just inviting errors or unexpected results. Now why can't the SQL team deprecate many of these allowed conversions? Are they mandated by ANSI-SQL?

    I see the need to convert varchars to datetime/datetime2/date/time, but then the format of the permissible input strings should be well-defined as well. I keep on struggling figuring out in all kinds of sample code or questions posted in this forum whether a string representation of a date is supposed to be DD/MM/YYYY or MM/DD/YYYY. Why not disallow such kinds of a conversion and make the ISO 8601 standard the only allowable input format in SQL 11? Come to it, I also hate these kinds of QotD's where one is asked what the result would be where types of operands are mixed and used in "arithmetic" operations. They are just teaching people very bad habits. But OK, I'm going off-topic here... 😉

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • TheRedneckDBA

    SSChampion

    Points: 14001

    Formatting issues aside, I thought this was a good brain teaser.

    The Redneck DBA

  • Charles Kincaid

    SSChampion

    Points: 13593

    I was not surprised by all the date manipulations.. What was surprising is that this question is really about UNION. :blink: It took me back noting that none of the options for answers, except for the "it throws an error" diversions talked about the contents of the rows returned.

    Glad you all got it right anyway.

    ATBCharles Kincaid

  • brewmanz

    SSCommitted

    Points: 1575

    Hasn't anybody noticed that the *none* of the 'weird datetime conversions' answer options actually gave the answer (1899-12-31 00:00:00.000) ??

  • Charles Kincaid

    SSChampion

    Points: 13593

    That was sort of my point. They all return the same thing. UNION takes out the duplicate rows. The answers pertain more to UNION than to the date time issue.

    It's one of those things when taking a test. Ever have one of those where you have a wordy question that runs nine paragraphs, defines 40 some-odd variables, and is confusing as anything? Then you look at the multiple answers (of which there are at least 4 dozen) only to see that the 11th from the bottom is: "This test is being taken in room 314A." Your instructions were to choose the best answer and that one is it!

    ATBCharles Kincaid

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

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