Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: DTS screws up dd/mm/yyyy dates

    Hi

    Unlike the colonies, we still use dd/mm/yyyy format in the UK. A bit like VB string concatentation, DTS likes to treat dd/mm/yyyy dates as mm/dd/yyyy if dd <=12.

    I've run into this problem twice and been stumped both times. The first time I was setting up a new DTS and switched to BULK INSERT instead. This time I was editing an existing DTS but was able to alter the CSV export to store the dates as YYYY-MM-DD. I appreciate this is ISO whatisface and ODBC canoni-watsit and is the ideal format to transfer dates between RDBMS systems but...

    is there a third way? Can you tell SQL Server to expect to see the incoming dates in DD/MM/YYYY format and respond accordingly? I expected the answer to be in the DateTimeString transformation but I cannot get it to work - errors (haven't got the precise error to hand but can supply).

    Not urgent. Not even important. Just curious

    TIA chaps
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you are purely in SQL Server, you can try setting the dateformat for the query:
    Code:
    set dateformat dmy
    select convert(datetime, '20/4/2006'), getdate()
    If this is in the VB code, or whatever equivalent you have, then I am not entirely certain what you can do beyond checking the locale settings of the machine it is running on.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    It's actually during the DTS transformation. This doesn't apply to that does it?

    You've pointed me towards something though. I correct for this in VB when working in that. Presumably I can write a script and use that as an ActiveX script for the transformation?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Yep. dateformat won't have any effect in a data pump task. I found an interesting bit of code over here:
    http://www.a1vbcode.com/snippet-3656.asp
    towards the bottom of the example that may help you along with reworking the transform task. Modifying the transform task is something that I have successfully avoided since about 1997 or so.

  5. #5
    Join Date
    Jun 2004
    Posts
    1

    Lightbulb System confused with dates

    The problem is with the 'intelligence' placed in the date parser in many db systems and not just SQL servers. The only way to 'defeat' the wrong dates is to specify all updates in SQL92 "dd MMM yyyy" form.

    Hence 01 Feb 2006 will always be 01/02/2006 (UK) or 02/01/2006 (US) unless the SQL coding guys have screwed up the parsing into localisation (in which case it is not SQL92 complient and should be avoided at all costs).

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've always been fond of the ISO standard 2006-04-20 because nearly everything either outright fails (old or poorly written code), or works fine (95 percent of everything I use).

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by mornao
    The problem is with the 'intelligence' placed in the date parser in many db systems and not just SQL servers. The only way to 'defeat' the wrong dates is to specify all updates in SQL92 "dd MMM yyyy" form.

    Hence 01 Feb 2006 will always be 01/02/2006 (UK) or 02/01/2006 (US) unless the SQL coding guys have screwed up the parsing into localisation (in which case it is not SQL92 complient and should be avoided at all costs).
    I'm just curious, but where in the standard did you find this particular date format referenced?

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    This time I was editing an existing DTS but was able to alter the CSV export to store the dates as YYYY-MM-DD. I appreciate this is ISO whatisface and ODBC canoni-watsit and is the ideal format to transfer dates between RDBMS systems
    Quote Originally Posted by Pat
    I've always been fond of the ISO standard 2006-04-20 because nearly everything either outright fails (old or poorly written code), or works fine (95 percent of everything I use).


    I pretty well always use ISO 8601. It just so happens that it was a bit of a pest to do so on this occasion and the name "Data Transformation Services" implies to me that I might be able to inform the process that incoming dates are dd/mm/yyyy.
    It is no biggie and if the response is "you are transferring data between two data sources - use the universally recognised format you moron" then I will quite merrily accept the advice.

    MCrowley - apologies for not getting back. I have to admit that it is because I never quite fathomed out how it related to the problem
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    No worries, Pootle. The code example (I thought, but might be wrong) converts or interprets currency according to the client's locale. You might be able to use the code that detects the local locale, and use that to convert/interpret the dates coming in.

    After re-reading your problem, the locale of the DTS client may not be the issue so much as the locale of the guy sending you this mess ;-).

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley
    No worries, Pootle. The code example (I thought, but might be wrong) converts or interprets currency according to the client's locale. You might be able to use the code that detects the local locale, and use that to convert/interpret the dates coming in.

    After re-reading your problem, the locale of the DTS client may not be the issue so much as the locale of the guy sending you this mess ;-).
    Lol - yup - converting isn't a problem (just a minor hassle) and the locale of the DTS client is known and, in my case, unvarying from server to server so it is probably a bit overkill as far as my situation is concerned.

    I will have a word with myself for sending me this mess To be fair - the outputting program merely picked up the local settings and patriotically outputted the dates in good old British DD/MM/YYYY. I have since told it to output in the rather more sterile YYYY-MM-DD.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •