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 ««12345»»»

Dating for DBAs - a second date Expand / Collapse
Author
Message
Posted Wednesday, April 13, 2011 12:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
CirquedeSQLeil (4/12/2011)
This is a good question - thanks Duncan


Thanks for the compliment Jason - glad you liked it.
Post #1092590
Posted Wednesday, April 13, 2011 12:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
Duncan Pryde (4/13/2011)
Henrico Bekker (4/12/2011)
All 4 options work? one of those questions again...pick 2 out of 4 correct answers...a quessing game again.


Try this:

SET DATEFORMAT ydm
GO
SELECT CAST('2011-04-28' AS DATETIME)

or this:

SET LANGUAGE British
GO
SELECT CAST('2011-04-28' AS DATETIME)

and do the same for the other date types, and you'll see that DATETIME and SMALLDATETIME produce an error while DATE and DATETIME2 don't.

So no guessing required!

Duncan


question doesnt state that you need to SET LANGUAGE....
anyway.


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1092591
Posted Wednesday, April 13, 2011 12:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 4,105, Visits: 5,463
Thanks for the question,
Good to get the brain juices working so early in the morning.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1092593
Posted Wednesday, April 13, 2011 12:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
Henrico Bekker (4/13/2011)

question doesnt state that you need to SET LANGUAGE....
anyway.


It says:

"regardless of any date format or language settings".

You change language settings using SET LANGUAGE and date format settings using SET DATEFORMAT.

Duncan
Post #1092595
Posted Wednesday, April 13, 2011 1:29 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:49 AM
Points: 1,443, Visits: 1,855
Duncan Pryde (4/13/2011)
Nakul Vachhrajani (4/13/2011)
Thank-you, Duncan for this wonderful series!


You're welcome - although I'd hesitate to call 2 questions a series! Unless you're expecting more?


Definitely expecting more Maybe I like them because a few years ago, we made major changes to our product to support globalization/internationalization and came across all these issues then. These QotDs are great learning opportunities for anyone who would want to work on a product that would work no matter where you take it.


Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #1092603
Posted Wednesday, April 13, 2011 1:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 2,589, Visits: 2,444
Thank you very much for this beautiful qotd.
It's unbelievable, but true, only new types DATE and DATETIME2 work in any case.
Post #1092604
Posted Wednesday, April 13, 2011 1:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 13,532, Visits: 11,328
Dammit, I got it wrong because I've misread the question.
Anyway, it was a great question, thanks Duncan.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1092612
Posted Wednesday, April 13, 2011 2:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:11 PM
Points: 1,940, Visits: 1,171
good Question

Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1092637
Posted Wednesday, April 13, 2011 3:06 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
Nakul Vachhrajani (4/13/2011)
Duncan Pryde (4/13/2011)
Nakul Vachhrajani (4/13/2011)
Thank-you, Duncan for this wonderful series!


You're welcome - although I'd hesitate to call 2 questions a series! Unless you're expecting more?


Definitely expecting more Maybe I like them because a few years ago, we made major changes to our product to support globalization/internationalization and came across all these issues then. These QotDs are great learning opportunities for anyone who would want to work on a product that would work no matter where you take it.



Thank you for these questions Duncan! And I am also expecting more .

Cheers
Post #1092648
Posted Wednesday, April 13, 2011 3:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:28 AM
Points: 1,248, Visits: 779
Great question perfectly worded. It was quite clear that dateformat was relevant to the question and that you were looking for only the 2 statements that would work under all conditions. I hate the pedantics on here.

However, without 2008 to play on, and after much unsuccessful digging around with google, I ended up guessing wrong. Bummer!

Learned something new though, which is always the primary objective

Keep them coming Duncan.


_____________________________________________________________________
"The difficult tasks we do immediately, the impossible takes a little longer"
Post #1092650
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse