DTS Date Format

I'm trying to do two things:  1) if a date is 1/1/1900 then make it blank. 2) if a date isn't 1/1/1900 then put it in the format yyyyMMdd (20020403).  My problem is the format part I can put it in a short or long date format but I can't get it in the format that I'm wanting.

Function Main()
  if DTSSource("IndividualTermDateDep") = "1/1/1900" then
     DTSDestination("IndividualTermDateDep") = ""
     Main = DTSTransformStat_OK
     DTSDestination("IndividualTermDateDep") = FormatDateTime(DTSSource("IndividualTermDateDep"),  2)
     Main = DTSTransformStat_OK
  end if
End Function  

Who is Participating?
curtis591Connect With a Mentor Commented:
You should be able to do it with the mid function I think.  

How about this does this work?  Example format

DTSDestination("IndividualTermDateDep") = mid(DTSSource("IndividualTermDateDep"),5,4)+mid(DTSSource("IndividualTermDateDep"),1,4)
When I do these types of things I put all the data into a temporary table before I load it into master table. I can then create views and run update statements to clean the data and that makes it easier than trying to do it in the dts package.  It is also very easy to loose your script on each field when you are changing the package.
yunginvAuthor Commented:
I understand that position but I'd really like to know how to do it in code instead of creating a temp table and then loading it into another table.  Surely there is a way to format the date.  

By the way I've tried using the VB code format(date, "yyyyMMdd") and got an error as well.

yunginvAuthor Commented:
I didn't even think of using the MID statment.  Thanks.

I still think there has to be a format statement that works but this is as good.

Again Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.