Date Format in DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Date Format in DTS

Author

Topic

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-05 : 04:53:05

The code below works fine but if we use a 4 digit year it fails !

The error we get is data overflow invalid cast etc, we are SQL2000

So what I am saying is that if I change
iyear = right(DTSSource("clindate"),2)
to
iyear = right(DTSSource("clindate"),4)
it fails !

Dim iDay
Dim imonth
Dim iyear
Dim dDate

Function Main()
iday = left(DTSSource("clindate"),2)
imonth = mid(DTSSource("clindate"),4,2)
iyear = right(DTSSource("clindate"),2)

dDAte = (iMonth + "/" + iday + "/" + iYear)

if isdate(dDate)<>True then
Main = DTSTransformStat_SkipRow
Else
DTSDestination("Clinic_Date") =dDate
Main = DTSTransformStat_OK
end if
End Function

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-07 : 07:13:14

What is in clindate?

Output the result and I bet you are getting a 2 digit year..

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-07 : 12:52:47

If I output the clindate as a string I get DD/MM/YYYY !

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 05:18:25

And it works if you only take 2 from the right?!?

Oh btw, Where are you setting tDate as it doesn't seem to be set here?

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-08 : 06:59:23

Rick

Yeah it works if I take 2 but not 4,
I cut the code down tDate should be dDate Sorry I corrected the post now

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 08:00:19

What about if you change


iyear = right(DTSSource("clindate"),2)


iyear = mid(DTSSource("clindate"),7,4)

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-18 : 05:12:49

Rick

I have moved onyo another project now but passed your suggestion on to the guy who is working on this one, I have told him to post up his findings here.

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources