Date Conversion Problem

I am importing a text file into a SQL table, using DTS. My problem is concerning the date fields. The source fields are in the yyyymmdd format. I have tried using datetime transformation, using yyyyMMdd as the source format, and MM/dd/yyyy as the destination formation. If there is a valid date, this works fine. However, many of the dates are either null or contain spaces, and the DTS will not handle them. Any suggestions as to how to handle this?Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?|||Originally posted by Brett Kaiser
Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?

-----------

The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.

I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.

Thanks for your suggestions.|||Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?|||Originally posted by Brett Kaiser
Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?

Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.|||Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?|||Originally posted by Brett Kaiser
Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?

No, I haven't used bcp before. I'll check it out.

In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:

Function Main()
If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
Main = DTSTransformStat_OK
End If
End Function

The error returned is: Invalid procedure call or argument - DTSSource|||Just wondering is your System a AS400 cause i had also ran into this before.|||Originally posted by hillcat
Just wondering is your System a AS400 cause i had also ran into this before.

No, PC with Windows XP Pro & SQL Server 2000|||but is the the mainframe file a rpg file|||Originally posted by hillcat
but is the the mainframe file a rpg file

I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.|||well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.|||Originally posted by hillcat
well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.

I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed|||My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0|||Originally posted by Brett Kaiser
My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0

I'll give it a try. Much thanks . . .|||Like:

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8))
GO

INSERT INTO myTable99(Col1)
SELECT 'yyyymmdd' UNION ALL
SELECT '20040317' UNION ALL
SELECT ' '
GO

-- Show me Valid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=1

-- Show me InValid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=0

--Move to it's Final Destination

CREATE TABLE myTable00(Col1 datetime)
GO

INSERT INTO myTable00(Col1)
SELECT Col1 FROM myTable99 WHERE ISDATE(Col1)=1

SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO