Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: vbscript and DTS date transformations

    Hi,

    I am trying to convert dates for import from a text file in a datetime field in SQL server. (i use UK format times)

    In the text file the date is in the format yyyymmdd which SQL server will not accept.

    I have tried using the script:

    ---------------------------------------------------------------------------

    Function Main()
    DTSDestination("Col001") = DTSSource("Col001")

    strDate = DTSSource("Col002")
    strYear = mid(strDate, 1, 4)
    strMonth = mid(strDate, 5, 2)
    strDay = mid(strDate, 7, 2)
    strDate = trim(strDay) + "/" + trim(strMonth) + "/" + trim(strYear)

    DTSDestination("Col002") = strDate

    Main = DTSTransformStat_OK
    End Function
    -----------------------------------------------------------------------------

    The script should format yyyymmdd as dd/mm/yyyy but this doesn't work giving the error:

    AxScriptXform encountered an invalid data value

    Can anyone help?

    Many thanks

  2. #2
    Join Date
    Oct 2003
    Posts
    3

    Thumbs up SQL/DTS Date Transformations

    Use the Monthname function to put the name of the month instead of the number.
    eg: 6 Oct 2003 instead of 6/10/2003

    strMonth = Left(Monthname(mid(strDate, 5, 2)), 3)
    will return the first three characters of the month.

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    >> In the text file the date is in the format yyyymmdd which SQL server will not accept.

    Are you sure about that? This is the only date format that can be reliably implicitely converted to a datetime so it should be ok.

  4. #4
    Join Date
    Oct 2003
    Posts
    3

    vbscript and DTS date transformations

    nigelrivett, when you use non-us dates, the date format yyyymmdd will not implicitly convert to the correct format unless you issue a SET DATFORMAT command prior to the conversion.

    Without using 'SET DATEFORMAT', the only sure way to ensure correct implicit conversion is to use the month name.

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Thanks guys. I have worked it out.

    Many thanks

  6. #6
    Join Date
    Jun 2002
    Location
    Belgium
    Posts
    43

    Re: vbscript and DTS date transformations

    Hi,

    Remember that MSSQL ALWAYS considers Dates in the american date
    notation mm/dd/yyyy. This means that even if you are using a local (programming) language or setting, that MSSQL will always try to compare / store dates in SELECTS and soforth in the above mentionned
    date-notation.

    Try using the format(datevariable,"mm/dd/yyyy") instruction in VB for example, before e.g. you create a SELECT string you send to build up a recordset.

    Success!

    VincentJS
    Gent, Belgium

  7. #7
    Join Date
    Oct 2003
    Posts
    3

    Re: vbscript and DTS date transformations

    My SQL Server installation seems to accept dates in the uk format i.e. dd/mm/yyyy from dts. Wierd!



    Originally posted by VincentJS
    Hi,

    Remember that MSSQL ALWAYS considers Dates in the american date
    notation mm/dd/yyyy.

    VincentJS
    Gent, Belgium

Posting Permissions

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