SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Weird datetime conversions


Weird datetime conversions

Author
Message
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3251 Visits: 6497
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)
TheRedneckDBA
TheRedneckDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4201 Visits: 2613
Formatting issues aside, I thought this was a good brain teaser.

The Redneck DBA
Charles Kincaid
Charles Kincaid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3279 Visits: 2384
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
brewmanz
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 406
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
Charles Kincaid
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3279 Visits: 2384
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
brewmanz
brewmanz
Right there with Babe
Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)Right there with Babe (765 reputation)

Group: General Forum Members
Points: 765 Visits: 406
Ah! I see ... Like those psychological tests where what they're testing is not what they tell you they're testing, because if you knew what they were *really* testing then you'd modify your behaviour which would invalidate what they were testing for.
macrostarrphish
macrostarrphish
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 155
None of the Above???

I received this as my results:

1899-12-31 00:00:00.000

Did the question change over time, too?
Isabelle2378
Isabelle2378
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2237 Visits: 516
I actually got that result too:
1899-12-31 00:00:00.000

Thanks!
Bea Isabelle
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search