March 14, 2002 at 1:34 pm
Hi everybody!
I'm trying to import data from DB2 to SQL7. Unfortunately I have some date fields in DB2, which are completely strange formatted but DB2 standard.
1020320 stands for march 20, 2002 🙂
So in my DTS package I import the data in one SQL7 table. And I want to format this char(7) type column which contains the date from DB2 in a SQL7 date type column. How can I do that?
I tryed this:
before was :
DTSDestination("data") = DTSSource("DZK8DT")
I commented out this line and instead I wrote this:
''' a exemple value is of DTSSource("DZK8DT") is: 20320
var = DTSSource("DZK8DT")
var = var + 20000000
yearvar = Left(var, 4)
monthvar = Mid(var, 5, 2)
dayvar = Right(var, 2)
colvalue = DateSerial(yearvar, monthvar, dayvar)
DTSDestination("DZK8DT") = colvalue
I receive a type mismatch error!!!
Thank you a lot,
Radu
Edited by - durug on 03/14/2002 2:41:36 PM
March 14, 2002 at 11:50 pm
I have a script for this at the office. If I forget to post by 11:00 am cst, drop me an email.
John
March 15, 2002 at 4:49 am
First do you know which line is failing? Try this change
var = CStr(DTSSource("DZK8DT") + 20000000)
yearvar = Left(var, 4)
monthvar = Mid(var, 5, 2)
dayvar = Right(var, 2)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 15, 2002 at 10:08 am
DuruG
The date format is a bit weird but... The first position is the century. If this is a 0 the year is 19 (if 1 then 20) & positions 2 & 3. The month is positions 4 & 5 and the day positions 6 & 7.
My function to convert this to a sql friendly date looks like this:
Dim SourceCol
Dim DestCol
Dim DestValue
SourceCol = "Col046"
DestCol = "Anniversary_Rate_Date"
If Left(DTSSource(SourceCol),1) = "1" then
DestValue = mid(DTSSource(SourceCol),4,2) + "/" + mid(DTSSource(SourceCol),6,2) + "/20" + mid(DTSSource(SourceCol),2,2)
Elseif Left(DTSSource(SourceCol),1) = "0" then
DestValue = mid(DTSSource(SourceCol),4,2) + "/" + mid(DTSSource(SourceCol),6,2) + "/19" + mid(DTSSource(SourceCol),2,2)
Else
DestValue = NULL
End If
If IsDate(DestValue) Then
DTSDestination(DestCol) = DestValue
Else
DTSDestination(DestCol) = NULL
End If
Main = DTSTransformStat_OK
Good luck
John
March 15, 2002 at 12:53 pm
If Left(DTSSource(SourceCol),1) = "1" then
DestValue = mid(DTSSource(SourceCol),4,2) + "/" + mid(DTSSource(SourceCol),6,2) + "/20" + mid(DTSSource(SourceCol),2,2)
Elseif Left(DTSSource(SourceCol),1) = "0" then
DestValue = mid(DTSSource(SourceCol),4,2) + "/" + mid(DTSSource(SourceCol),6,2) + "/19" + mid(DTSSource(SourceCol),2,2)
Else
DestValue = NULL
End If
Familiar with this since we get it from an Aspect sitch that way. Try this then
DestValue = CInt(DTSSource(SourceCol)) + 19000000
This should get into a format of
0991231 = 19991231
1010806 = 20010806
etc
If the column can be null then do this (I think left will throw an error when it is null)
DestValue = Null
If LEN(DTSSource(SourceCol)) > 0 Then DestValue = CInt(DTSSource(SourceCol)) + 19000000
The reason I use LEN is that all vbscript variables are stored as variant and variant being a btreive object store the length at the head of the object, thus LEN executes faster then checking other ways.
Then do
If IsDate(DestValue) Then
DTSDestination(DestCol) = DestValue
Else
DTSDestination(DestCol) = NULL
End If
If you still get an error IsDate may be the cause try
If Len(DestCol) > 0 Then
If IsDate(DestValue) Then
DTSDestination(DestCol) = DestValue
Else
DTSDestination(DestCol) = NULL
End If
Else
DTSDestination(DestCol) = NULL
End If
This should do it. Try and let me know.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 15, 2002 at 1:41 pm
Thanks very much. You helped me a lot.
First time DTS, so... sorry!
Radu
March 15, 2002 at 2:10 pm
That's cool, there is just so much to know it will always seem like the first time some days.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 15, 2002 at 3:08 pm
'This will not error
if left(null, 1) = 1 then
msbox "boo hoo"
else
msgbox "woo hoo"
end if
'neither will this
msgbox isdate(null)
'This will error
msgbox Len(Null) 'invalid use of null
'finally,
DestValue = CInt(DTSSource(SourceCol)) + 19000000
CInt will blow up if there is an alpha character in the data. If you can trust your data source, do the math. If you can't, parse the string.
Good luck
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply