Post reply

QOD dec 19

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

  • December 19, 2003 at 2:17 am

    #487157

    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.

  • December 19, 2003 at 2:57 am

    #487169

    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'

  • December 19, 2003 at 5:29 am

    #487188

    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]

  • December 19, 2003 at 7:12 am

    #487203

    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]

  • December 19, 2003 at 7:22 am

    #487204

    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.

  • December 19, 2003 at 8:15 am

    #487224

    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.

  • December 19, 2003 at 8:35 am

    #487231

    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

  • December 19, 2003 at 9:04 am

    #487242

    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!

  • December 19, 2003 at 3:18 pm

    #487310

    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 🙂

  • December 19, 2003 at 3:57 pm

    #487322

    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.

  • December 22, 2003 at 12:35 am

    #487382

    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

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

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