How to convert flat file date ("041304") into sql Server date format in DTS

Posted on 2006-05-24
Last Modified: 2006-11-18
Hi ,
    My client is sending one flat file which will be having one column called date field but in that date is coming like "041304".  But through DTS i want to send it to my SQL Server table like this "13-Apr-2004".  How to translate this ?
   Do you think putting ActiveX script in middle and implement below logic is suggestable ?

                    Dim str
                    str = DTSSource("myDate") ' 041304
                    str = Mid(str, 1, 2) & "-" & Mid(str, 3, 2) & "-" & Mid(str, 5, 2) '04-13-04

Please suggest me good solution in this

Question by:bethamsetty
    LVL 5

    Accepted Solution

    if performance is not an issue (small file, a relatively long time available for this job to run, etc..) than... yes, it is a good solution.
    LVL 28

    Expert Comment

    import the data into temporary table in sql server and then
    update the column in the temporary table as

    update temptable set flatfiledatecolumns = substring(flatfiledatecolumns , 3,2) + '-' + left(flatfiledatecolumns ,2)+'-20'+right(flatfiledatecolumns ,2)

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now