asked on

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

curtis591

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.

yunginv

ASKER

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

ASKER CERTIFIED SOLUTION

curtis591

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yunginv

ASKER

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