May 6, 2009 at 8:38 pm
Comments posted to this topic are about the item Weird datetime conversions
May 6, 2009 at 9:41 pm
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 '<'.
May 6, 2009 at 10:00 pm
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]
May 6, 2009 at 10:10 pm
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...?
May 6, 2009 at 10:14 pm
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. 😉
May 6, 2009 at 11:57 pm
your question may lead confusion as you are usign third party tool to write the query. your TSQL include code inside the TSQL statement.
May 7, 2009 at 12:54 am
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
May 7, 2009 at 2:16 am
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.
May 7, 2009 at 2:33 am
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.
May 7, 2009 at 3:00 am
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:
May 7, 2009 at 3:53 am
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... 😉
May 7, 2009 at 7:28 am
Formatting issues aside, I thought this was a good brain teaser.
The Redneck DBA
May 8, 2009 at 11:56 am
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
May 8, 2009 at 7:17 pm
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) ??
May 12, 2009 at 7:39 am
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 17 total)
You must be logged in to reply to this topic. Login to reply