QOD 1/8/03 (or is it 8/1/03?)

Question

QOD 1/8/03 (or is it 8/1/03?)

mccork

SSC-Insane

Points: 22288
More actions
July 31, 2003 at 11:07 pm

#106008

I got this question right only because (fortunately) I made allowance for the fact that you Americans don't recognise internationally accepted date formats.
Option 4 doesn't work on my server down here in the lucky country.
Where'd you guys come up with the mm/dd/yy date format anyway? Anytime now I'm expecting to see you introduce the mm:hh:ss time format.
Cheers,
- Mark

Cheers,
- Mark

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Lancea Say Hey Kid Points: 669 More actions · Answer 1

I'm not too keen on the answers either. I always insist that dates are ANSI format (YYYYMMDD) or another unambiguous format (e.g. 01-Jan-2003).

Lance

ErikBr Right there with Babe Points: 726 More actions · Answer 2

I'm not too keen on the answers either. I always insist that dates are ANSI format (YYYYMMDD) or another unambiguous format (e.g. 01-Jan-2003).

I totally agree with Lance

Erik

BanzaiSi SSChasing Mays Points: 611 More actions · Answer 3

My preferred alternative (and a variation on choice 4) is to do:

WHERE CAST(CONVERT(char(8), DateVal, 112) AS datetime) = '2003-08-01'

Mainly because I prefer the intermediate character format to casting to a float. It may be a touch slower, but it is documented to produce consistent results, rather than the cast to a float, which I would rather not rely on.

--

Si Chan

Database Administrator

Brian Knight One Orange Chip Points: 27249 More actions · Answer 4

What's funny is the question was written by someone in Germany

Brian Knight

bknight@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bknight

Brian Knight
Free SQL Server Training Webinars

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
What's funny is the question was written by someone in Germany

is that true ???

Cheers,

Frank

Well, looking at the last name, it obviously could be. Looking only at the first name I thought of someone from Skandinavia.

To be honest, I was wrong with this question

Edited by - a5xo3z1 on 08/01/2003 05:02:21 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

bgrover Old Hand Points: 382 More actions · Answer 6

We actually wrote a function that does what si.chan does. We use it a lot to address this issue.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

I agree thou that the ISO format yyyymmdd should be used in questions as there are various reasons why this doesn't cause issues. I hadn't htought about it before myself but when I came across a BETWEEN yielding dates in other years but in the right range I realized it was because of the mm/dd/yyyy.

Interesting read

http://www.geocities.com/jusjih/iso8601.html

As for the origin some people on the web have jokingly stated "due to their need to be differenr". However I feel it is most likely a hold over from some war (maybe the war for independence). COnsider if the British commanders where using dd/mm/yyyy and an Colony commander want to send a meeting note for a date and time the best way too fool people would be to flip dd/mm to mm/dd so if they want to meet Jan 2 they send 1/2/xxxx and the British think they are meeting Feb 1st thus the enemy is going to show up at the wrong time. Besides most of this techniques usually are later adopted from military as people get used to it. At least that is my theory.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

The whole discussion with date formats reminds me somehow of those dbase days where I used to split the string into the ISO format.

Even today I use this format when creating folders in the filesystem.

And now ?

Some 10, 15 or even 20 years later ?

Funny, the datetime datatype solved problems we didn't have without

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lender1 SSC Journeyman Points: 78 More actions · Answer 9

I m confused. Can't i use Select .... where DATEDIFF( D,DateVal,'08/01/2003')=0

What is the harm if i used this?

Ritch Ten Centuries Points: 1044 More actions · Answer 10

quote:
I m confused. Can't i use Select .... where DATEDIFF( D,DateVal,'08/01/2003')=0
What is the harm if i used this?

None thats what I always use, and why I got this q wrong. Using datediff is better when confronted by different datetime formats in the same db.

laters

*I didn't do anything it just got complicated*

"I didn't do anything it just got complicated" - M Edwards

Jags2001 SSCommitted Points: 1529 More actions · Answer 11

So the correct answer is None of the above.

(The proposed solutions are all politically incorrect or non-ISO compliant.)

When you deliver an application for international usage (who doesn't?), the best approach is to use CONVERT with either the ISO or the ODBC canonical formats with the century. (120 or 112) Never compare a date to string. Always compare a date to date.Do not assume implicit conversion to work for you.(I see Oracle pundits cheering!)

See this http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp

SQLBill SSC Guru Points: 51440 More actions · Answer 12

It's probably from when we seceded from England. Changed the way we drive, changed the way we write dates.

-SQLBill

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

quote:
It's probably from when we seceded from England. Changed the way we drive, changed the way we write dates.

Changing the way to drive is obviously a plus,....

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Hans Lindgren SSChampion Points: 10496 More actions · Answer 14

Note to self: Investigate the most commonly used vehicle to drive on the left side in Britan before the war of independance...