Weird datetime conversions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Anders Hellner » Weird datetime conversions

18 posts, Page 2 of 2

««1 2

Weird datetime conversions

Rate Topic

Display Mode

Topic Options

Author

Message

Jan Van der Eecken

Posted Thursday, May 07, 2009 3:53 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:52 PM
Points: 2,303, Visits: 6,082

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... Wink

--------------------------------------------------------------------------

The function of good software is to make the complex appear to be simple. (Grady Booch)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)

Post #711861

Jason Shadonix

Posted Thursday, May 07, 2009 7:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:49 AM
Points: 1,409, Visits: 2,032

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

Jason Shadonix
MCTS, SQL 2005

Post #712017

Charles Kincaid

Posted Friday, May 08, 2009 11:56 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:56 AM
Points: 772, Visits: 1,828

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.

ATB

Charles Kincaid

Post #713231

brewmanz

Posted Friday, May 08, 2009 7:17 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 11:16 AM
Points: 477, Visits: 389

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

Charles Kincaid

Posted Tuesday, May 12, 2009 7:39 AM

Right there with Babe

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:56 AM
Points: 772, Visits: 1,828

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

brewmanz

Posted Tuesday, May 12, 2009 2:57 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 11:16 AM
Points: 477, Visits: 389

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