[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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

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
                    DTSDestination("myDate")=cdate(str)

Please suggest me good solution in this

Regards
Sridhar.B
0
bethamsetty
Asked:
bethamsetty
1 Solution
 
ewildeCommented:
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.
0
 
imran_fastCommented:
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)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now