QOD dec 19

Question

QOD dec 19

ErikBr

Right there with Babe

Points: 726
More actions
December 19, 2003 at 1:32 am

#106665

Our solution to this problem is always use the following date format :
'19 dec 2003' or 'dec 19 2003'.
If you use three letters for the month, there will be no problem for either date format.
Erik.

Viewing 12 posts - 1 through 11 (of 11 total)

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

sfm Old Hand Points: 394 More actions · Answer 1

I had the same type of problem recently and I basically just converted the the date before insertion. The given solution improves upon this basically because you don't have to convert multiple dates in a given connection.

Kelvin Lush SSCrazy Points: 2602 More actions · Answer 2

We also have a policy of always using character months (eg '19 Dec 2003'), however, we came a cropper when we opened an office in Switzerland - where they use French months - and the server didn't understand '19 Dec 2003'

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

What is ASAIK?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

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

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

Oh, a typo! I was already searching...

Frank

http://www.insidesql.de

http://www.familienzirkus.de

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

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 5

Of course, SET DATEFORMAT could also be used to change the default date format. I don't know what else changes when SET LANGUAGE is used.

jgsqlpro Old Hand Points: 310 More actions · Answer 6

I agree, I did not answer the question because it did not include changing the dateformat for the imports from overseas using set dateformat dmy, and set dateformat mdy for the US imports.

SQLBill SSC Guru Points: 51440 More actions · Answer 7

quote:
I agree, I did not answer the question because it did not include changing the dateformat for the imports from overseas using set dateformat dmy, and set dateformat mdy for the US imports.

But like any question (even the MS Certifications), the correct answer is always (okay, it's supposed to be) the BEST choice of the answers supplied. So, just because the MOST CORRECT answer may not have been given, doesn't mean there isn't a solution among the answers that do exist.

-SQLBill

stubob SSCommitted Points: 1839 More actions · Answer 8

Good points - in the perfect world we would all use the month name (JAN, FEB, etc..) or YYYYMMDD formats. (YYYYMMDD also sorts well as a number or character string)

However, people insist on using the mm/dd/yy in the USA & dd/mm/yy in much of the rest of the world. The question asks "What is the best way to correct the problem without impacting the rest of the organization?"

Forcing the rest of the organization to follow a new dating scheme, which may be incompatible with existing systems, would certainly have an impact.

Given the constraints, the best way is to convert to standard date-time format as shown.

The only normal people are those you don't know well - Oscar Wilde

Data: Easy to spill, hard to clean up!

ChrisMaene Old Hand Points: 349 More actions · Answer 9

I have only one solution for this issue: ISO.

If you set the date format to YYYYMMDD, all your date troubles will go away. It's possible that other formats work in a high percentage of cases, but what do you do if you are faced with a non-english version of SQL Server (believe me, it matters!!!) Will formats like '19 dec 2003' also work in Japan or the Czech Republic??? I seriously doubt it. No offense intended 🙂

theJapester Valued Member Points: 65 More actions · Answer 10

I agree with AdiCohn's type of approach.

The standard ODBC Canonical format (the convert function style: 120) is yyyy-mm-dd hh:mi:ss(24h) and never needs to be converted back from that format in any version of SQL Server back to at least 6.5.

A quick and dirty approach to extracting just the date data in a datetime (until we get a date only datatype in Yukon!) is this:

CONVERT ( char(10), DateOrdered, 120 )

Very unambiguous, even across the pond.

---
Resident DBMS fence sitter.

gserdijn Hall of Fame Points: 3290 More actions · Answer 11

What are the consequences of using SET DATEFORMAT or SET LANGUAGE for Stored Procedures?

We noticed that using SET DATEFORMAT dmy; resulted in a decrease in performance, possibly due to recompilation of the Stored Procedure.

Is this correct? Maybe a clue where to find more info?

Kind regards, Gerry

Dutch Anti-RBAR League