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


Dating for DBAs


Dating for DBAs

Author
Message
webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4116 Visits: 3878
This was a great question. I got it wrong but appreciate the information. Thanks!

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4796 Visits: 72518
cdiebolt (4/6/2011)
Add one more european developer! ;-)
I didn't know that the 2nd answer is dateformat dependent. Whistling


Me either... I was pretty sure 2011-04-28 was a dateformat neutral date... Sad

Just a silly ANSI standard, but not dateformat neutral... :/

Of course I missed the hint (select 2)... which might have helped.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 1046
UMG Developer (4/5/2011)
CirquedeSQLeil (4/5/2011)
UMG Developer (4/5/2011)
How many people actually use the ODBC date literals?


I think very few in comparison.


Yeah, I think the number of people answering correctly shows that. (Currently 33% of the 24 people that have answered.)


I would have to agree that using the ODBC date literals is "Old School" in a bad way.

I had a project back in the late 90's that had to show dates in Cannadian and US format.
We used the second format to transfer the dates as strings from the UI to SQL and back.

This yyyyddmm string format tested on SQL 7 and SQL 2000 as being 50% faster during a load.
I tested today when I saw the question and it still round trip faster to format the date as string in the yyyyddmm format and pass to SQL for Multilaguage and Multiformat support. Formating as string YYYY-dd-mm and doing a cast using ODBC date literals just takes longer.
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4277 Visits: 3648
Great question. Love the title.
SanDroid
SanDroid
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 1046
paul.knibbs (4/6/2011)
Pity you can't split the results by nationality--I wonder how many British or European DBAs would get this one wrong? :-)

I would bet it is a greater percentage than US dba's.
Many US dba's and database developers have worked on at least one project with Canadian customers. They use the dd-mm-yyyy data format. :smooth:
It is my understanding the all of Great Briton uses the same date format.
Surii
Surii
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 489
Excellent question. Got it wrong though, but learned something new today.

Thanks,
Suresh
Rich Mechaber
Rich Mechaber
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1547 Visits: 3665
Duncan Pryde (4/6/2011)
I'm glad people are liking the question - thanks for the positive comments everyone.

I was motivated to come up with the question after following a discussion relating to a QotD from a few weeks ago. There was some (at times quite heated!) debate around language-independent date formats, so I thought it would be good material for a question, and hopefully some more discussion.

Incidentally, there's a "second date" scheduled to be published next week, so anything you read in those links could be handy... Cool


OK, Duncan, I got this wrong, and I'm the one who posted the link last week to Tibor Karaszi's excellent write-up!Ermm

I don't use the {} ODBC escape format, so I missed that one. Guess I learned something, so thanks.

Rich
mtillman-921105
mtillman-921105
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 3852
Terrific question. For some reason dates are not my best topic.

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1615 Visits: 1062
Duncan Pryde (4/6/2011)
I'm glad people are liking the question - thanks for the positive comments everyone.

I was motivated to come up with the question after following a discussion relating to a QotD from a few weeks ago. There was some (at times quite heated!) debate around language-independent date formats, so I thought it would be good material for a question, and hopefully some more discussion.

Incidentally, there's a "second date" scheduled to be published next week, so anything you read in those links could be handy... Cool


I remembered that discussion and got the answer correct as a result! (I knew enough to test these possibilities out in SSMS with language set to British and then to French because I knew they would probably all work with my current setting of US English.) Two points is a good reward for reading, retaining, and applying information introduced in QOTD discussions.
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3578 Visits: 1552
rmechaber (4/6/2011)
Duncan Pryde (4/6/2011)
I'm glad people are liking the question - thanks for the positive comments everyone.

I was motivated to come up with the question after following a discussion relating to a QotD from a few weeks ago. There was some (at times quite heated!) debate around language-independent date formats, so I thought it would be good material for a question, and hopefully some more discussion.

Incidentally, there's a "second date" scheduled to be published next week, so anything you read in those links could be handy... Cool


OK, Duncan, I got this wrong, and I'm the one who posted the link last week to Tibor Karaszi's excellent write-up!Ermm

I don't use the {} ODBC escape format, so I missed that one. Guess I learned something, so thanks.

Rich


Hi Rich - I couldn't remember who it was who'd posted the link, but after dipping into it a few times and reading the BOL page, a couple of questions came to mind - so thanks for sowing the seed!
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