I am trying to import data into SQL 2000 through an ODBC and keep getting an
error, I tracked down the error and it is because the program that I am
trying to pull data from has invalid dates (by design, not mine). It uses the
31st day in all months. I am trying to get figure out how to correct the
dates to the first day of the next month. I have tried the following in the
ActiveX Transformation, but it did not work.

InvMonth = Month(DTSSource("DB_KEY2_PRI_INV_DATE_8"))
InvDay = Day(DTSSource("DB_KEY2_PRI_INV_DATE_8"))
InvYear = Year(DTSSource("DB_KEY2_PRI_INV_DATE_8"))
InvNewMonth = InvMonth + 1
If InvMonth = 02 and InvDay > 28 then
DTSDestination("DB_KEY2_PRI_INV_DATE_8") = InvYear + "-" + InvNewMonth + "-"
+ 01
else if InvMonth = 04 and InvDay > 30 then
DTSDestination("DB_KEY2_PRI_INV_DATE_8") = InvYear + "-" + InvNewMonth + "-"
+ 01
else if InvMonth = 06 and InvDay > 30 then
DTSDestination("DB_KEY2_PRI_INV_DATE_8") = InvYear + "-" + InvNewMonth + "-"
+ 01
else if InvMonth = 09 and InvDay > 30 then
DTSDestination("DB_KEY2_PRI_INV_DATE_8") = InvYear + "-" + InvNewMonth +
"-" + 01
else if InvMonth = 11 and InvDay > 30 then
DTSDestination("DB_KEY2_PRI_INV_DATE_8") = InvYear + "-" + InvNewMonth +
"-" + 01
else
DTSDestination("DB_KEY2_PRI_INV_DATE_8") =
DTSSource("DB_KEY2_PRI_INV_DATE_8")
End If

Any help would be greatly appreciated.

I can't see how adding integer values (the fcns Year, Day, month return
Integer values) and to "-" chars is going to give you what you expect?
If this is really the code you're using then make sure you're getting
the date out (DTSSource("DB_KEY2_PRI_INV_DATE_8")) the way you expect.
Look at the differences here and see if you don't have a similiar thing
going on

declare @invmo int, @invday int, @invyear int
set @invmo = Month(getdate())
set @invday = Day(getdate())
set @invyear = Year(getdate())
select @invmo + "-" + @invday + "-" + @invyear
print "versus"
select convert(varchar, @invmo) + "-" + convert(varchar, @invday) + "-"
+ convert(varchar, @invyear)

Returns

-----------
2037

versus


--------------------------------------------------------------------------------------------

12-20-2005

Determine your desired output format and work from there.

Urchine, I can't even get it to the point that it is giving anything back,
because there are invalid dates to begin with it errors out.
Let me try to explain this a little more clearly, I may be way off in the
way that I am trying to fix the issue. I am trying to use DTS to pull dates
into SQL Server from an ODBC connection. The issue that I am having is that
the dates in the column on the ODBC are sometimes invalid such as
"2005-09-31" (only 30 days in Sept.). When you pull this information into an
Access database, it takes the invalid dates and changes them to the first
date of the following month, so, the 2005-09-31 would be converted to
2005-10-01. DTS is not that intuitive and I need to figure out how to tell it
that if it finds an invalid date to change it to the first of the next month,
if possible.

Thank you

"urchin" wrote:

OK the approach you're using is fine imo (obviously changing
the source data to stop creating invalid dates is ideal but
may not be possible in your situation). Use an ActiveX
transformation like you're doing, just generate the
DTSSource("DB_KEY2_PRI_INV_DATE_8") value as a string. The
VBScript functions IsDate and DateAdd are key here...

' If the source date is valid, use it
if not IsDate(DTSSource("DB_KEY2_PRI_INV_DATE_8")) then
' Assume its always the day that might be incorrect, so
' get the Yr and Mo and set the day to the 1st since
' every month has a 1st (assuming dates can be formatted
' YYYY-MM-DD in VBScript, if not use Left/Mid/Right fcns
' to massage source into format you need)
CorrectedDate = Left(DTSSource("DB_KEY2_PRI_INV_DATE_8"), 7) & "-01"

' Now youve got YYYY-MM-01, to get the last day of this
' month simply add one to the month then -1 to that day;
' this counts leap years and all that other crud
CorrectedDate = DateAdd("D", -1, DateAdd("M", 1, CorrectedDate))
DTSDestination("DB_KEY2_PRI_INV_DATE_8") = CorrectedDate
else
DTSDestination("DB_KEY2_PRI_INV_DATE_8") =
DTSSource("DB_KEY2_PRI_INV_DATE_8")
end if

Hope this helps!