Go Premium for a chance to win a PS4. Enter to Win

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

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
  else
     DTSDestination("IndividualTermDateDep") = FormatDateTime(DTSSource("IndividualTermDateDep"),  2)
     Main = DTSTransformStat_OK
  end if
End Function  


Thanks
yunginv
0
yunginv
Asked:
yunginv
  • 2
  • 2
1 Solution
 
curtis591Commented:
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.
0
 
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.

yunginv
0
 
curtis591Commented:
You should be able to do it with the mid function I think.  

How about this does this work?  Example format
mmddyyyy
01012002


DTSDestination("IndividualTermDateDep") = mid(DTSSource("IndividualTermDateDep"),5,4)+mid(DTSSource("IndividualTermDateDep"),1,4)
0
 
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
yunginv
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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