Post reply

Change Date format in DTS

  • September 2, 2005 at 6:41 am

    #67477

    Guys hi,

    i need your help on this...

    i am copying data from a ascii file to a table.

    the ascii file has the dates in the following format

    22-3-1998. This is not recognized as a date since it has dashes.

    If i try to convert this ascii column into a date format using cdate, then i get the date but in the american "way" 3/22/1998. How can i tranform it to a dateformat like 16/3/1998?

    Thanks in advance,

    Dionisis

    "If you want to get to the top, prepare to kiss alot of bottom"

  • September 2, 2005 at 7:06 am

    #587167

    Have you looked at SELECT CONVERT(VARCHAR(50), GETDATE(), 103)?



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • September 2, 2005 at 7:13 am

    #587173

    i meant with active x (within a DTS package)... 🙂

    I have used formatdatetime (vb) but it depends on the server's regional settings... anything better than that?

    sorry for not mentioning active x AJ, .. 🙂

    "If you want to get to the top, prepare to kiss alot of bottom"

  • September 2, 2005 at 9:33 am

    #587291

    using active x put the date 8-8-1999 format in a variable, do a replace of the - to / .  next do a isdate on the field.  if it passes save the in the dest field.

  • September 5, 2005 at 5:24 pm

    #587638

    How about using the built-in Datetime transformation task ??

     

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

  • September 5, 2005 at 8:01 pm

    #587646

    Agree with Phill, I have used the built in datetime transform when dealing with crap data coming in from an Oracle SAP system.  You can specify a number of formats of datetime to covert to. 

    Select transform data task properties and then under transformations choose new and select date time string.

    Derek

  • September 6, 2005 at 1:18 am

    #587679

    That did it for me as well Guys! 🙂 Cheers! 🙂

    The script (activeX vbscript in a DTS package), in case someone is interested is as follows..

    Function Main()

    if Left( DTSSource("Source Column Name"), 2) = " " then

    DTSDestination("Destination Field") = null

    Else DTSDestination("ADM_DATE") = FormatDateTime( DTSSource("Col008") , 2)

    end if

    Main = DTSTransformStat_OK

    End Function

    "If you want to get to the top, prepare to kiss alot of bottom"

Viewing 7 posts - 1 through 6 (of 6 total)

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