Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Weird datetime conversions Expand / Collapse
Author
Message
Posted Thursday, May 7, 2009 3:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 17, 2014 7:18 AM
Points: 2,439, Visits: 6,426
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)
Post #711861
Posted Thursday, May 7, 2009 7:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 1,491, Visits: 2,114
Formatting issues aside, I thought this was a good brain teaser.

Jason Shadonix
MCTS, SQL 2005
Post #712017
Posted Friday, May 8, 2009 11:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
I was not surprised by all the date manipulations.. What was surprising is that this question is really about UNION. 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.


ATB

Charles Kincaid

Post #713231
Posted Friday, May 8, 2009 7:17 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:03 AM
Points: 488, Visits: 403
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) ??
Post #713444
Posted Tuesday, May 12, 2009 7:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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!


ATB

Charles Kincaid

Post #715038
Posted Tuesday, May 12, 2009 2:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:03 AM
Points: 488, Visits: 403
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.
Post #715487
Posted Thursday, July 9, 2009 11:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:01 PM
Points: 96, Visits: 85
None of the Above???

I received this as my results:

1899-12-31 00:00:00.000

Did the question change over time, too?
Post #750506
Posted Wednesday, September 23, 2009 5:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:42 AM
Points: 1,130, Visits: 487
I actually got that result too:
1899-12-31 00:00:00.000



Thanks!
Bea Isabelle
Post #792998
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse