Post reply

Converting varchar to datetime in Active X transfo

  • January 22, 2003 at 8:38 am

    #106130

    I'm on a SQL Server 7 DB currently importing a flat file with a date format of yyyymmdd. I'm trying a Active X transformation to bring it in as datetime with a dd-mmm-yy format. I'm trying to import the file into a Oracle DB using SQL Server DB as staging area.

    Below is my Active X script:

    if isnull (DTSsource("SOL_BIRTH_DT").value) then

    DTSDestination("SOL_BIRTH_DT") = DTSsource("SOL_BIRTH_DT")

    else

    DTSDestination("SOL_BIRTH_DT") = cast (right (DTSsource("SOL_BIRTH_DT"),2) & "-" & mid(DTSsource("SOL_BIRTH_DT"),5,2) & "-" & mid(DTSsource("SOL_BIRTH_DT"),2,2) as datetime)

    end if

  • January 22, 2003 at 2:42 pm

    #446996

    Instead of the Cast statement, use DateSerial. I'd also use the full 4 digit year.

    So your statement would end up like,

    DTSDestination("SOL_BIRTH_DT") = DateSerial( Left(DTSsource("SOL_BIRTH_DT"),4), mid(DTSsource("SOL_BIRTH_DT"),5,2), right (DTSsource("SOL_BIRTH_DT"),2))

    Thanks

    Phill Carter

    --------------------
    Colt 45 - the original point and click interface

Viewing 2 posts - 1 through 1 (of 1 total)

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