Date issues in DTS

[charlise]

Hello Everyone,
I am trying to load data from a text file into a datetime field in a table in SQL Server. The text file contains values in the format yyyymmdd so I know I can use a Date String function in order to load them in the format yyyy-mm-dd. The problem is that I have some values in the text file that are 00000000. I'm using ActiveX to load "" in the datetime field when the value from the source file is 00000000, but how do I handle the case when it is also a regular date in the format 20041011. It's like I can use the Date String function to load the data as long as it's not 00000000, and I can load the data if it's only 00000000, but I can't handle when the data is both 00000000 and a regular date like 20041011. Can anyone help?

 

[SQLDenis]

replace 00000000 with 19000101
since that's what's going in the DB anyway
run this in QA

declare @d datetime
select @d =00000000
select @d

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[charlise]

I'm not sure I understand. How do I make sure that as it's loading it doesn't put in the 00000000 and that it recognizes 20041011 as a date field? If I don't tell it how to handle these things as it's loading I noticed I got errors in both those cases.

 

[SQLDenis]

just change it to string and it should work, you don't need to use the Date String function

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[charlise]

SQLDenis when you say change it to string, what do you mean? I don't understand why I don't need to use the Date String function? Also is there a way I can use a function in VBScript to change the source column from yyyymmdd (not a date) to yyyy-mm-dd to go into the datetime field?

Please help! I'm still getting errors!

 

[Rhys666]

As a really very basic start, something like this...

if DTSSource("Col001") = "00000000" then
	DTSDestination("MyDateTime") = "1900-01-01"
else
	DTSDestination("MyDateTime") = Left( DTSSource("Col001") , 4 ) & "-" & Mid( DTSSource("Col001") , 5 , 2 ) & "-" & Right( DTSSource("Col001") , 2 )
end if

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra
If life were fair, Dan Quayle would be making a living asking 'Do you want fries with that?' John Cleese

 

[charlise]

Thanks Rhys666. I didn't know how to parse the string in order to "make" it look like a date. That works! Thanks for your help.

 

[Rhys666]

That is pretty basic and will only work on correctly formatted dates. It will fall over blank entries but it shouldn't be too difficult to process these as well.

Glad to Help!

Rhys
The use of COBOL cripples the mind; its teaching should, therefore, be regarded as a criminal offense Edsgar Dijkstra
If life were fair, Dan Quayle would be making a living asking 'Do you want fries with that?' John Cleese