Transfer text data to datetime format

Question

Transfer text data to datetime format

fmantao

SSC-Addicted

Points: 406
More actions
July 15, 2004 at 12:23 pm

#63412

This is a newbie DTS question. We receive data from an external entity in flat file text format which we need to import into our database tables.

The data in these files contain date columns which are sent as a varchar(10) character in the format ‘YYYYMMDD ‘ (Note that the last two characters are spaces).

Using DTS I tried to import the data but am having problems with these dates and the data will not load. I read something about using the CDATE function but could not find any examples on how to use it.

Any help would be appreciated.

Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 1

Whenever you're creating your transformation from the txt file to your db table, for that specific column of data, try using "DateTime String" instead of using the "Copy Column" transformation. You can also change the format of the data by going into Properties while in this specific transformation.

fmantao SSC-Addicted Points: 406 More actions · Answer 2

Currently when using DTS, I am doing so using the wizard. All I do is choose a source, namely the text flat file on the server and then I select the fixed field format and then set the columns. After that I choose the destination SQL Server.

At this point I have tried to transform the data as listed below:

DTSDestination("INVOICE_DATE") = DTSSource("Col004")

I changed the above statement to:

DTSDestination("INVOICE_DATE") = CDATE(DTSSource("Col004"))

Neither of these two statements worked. I do not see any "DateTime String" option available. Please tell me if I am missing something.

Thanks

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 3

OK, so you're using the DTS Import/Export Wizard.

My first thought is to try what you're doing but also add the RTRIM function to strip off the trailing spaces, like...

DTSDestination("INVOICE_DATE") = CDATE(RTRIM(DTSSource("Col004")))

If this does not work, try saving the DTS and then open the DTS in the Local Packages under Data Transformation Services. Then you can get into the transformations of each column and change it to "DateTime String".

Try this and let me know what happens.

fmantao SSC-Addicted Points: 406 More actions · Answer 4

The DTS package created using the wizard errored out with:

'Type mismatched: CDATE'

Would this be because of the format sent, namely 'YYYYMMDD'. Do we have to convert this into something else?

Thanks

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 5

I've looked at this from within Access to test the CDATE function and it does not like the YYYYMMDD format. It needs to have something like YYYY/MM/DD with the slashes in order for it to work properly.

With that said, you might just pull in the data as is without any conversion as a first step and then do whatever manipulation of the data in a second step to get it into the format you desire.

Another thought is to change the transformation to parse out each portion of the date and concatenate the slash along with the function to have CDATE to work properly.

This would look something like...

CDATE( LEFT(DTSSource("Col004"),4) & "/" MID(DTSSource("Col ... etc...

I'm not sure if this would work in the transformation, but it might be worth a look.

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 6

If you try

declare @a datetime

select @a = '19041112 '

select @a

in query analyser you will see that SQL server has no problem with the format of the dates (even with 2 spaces on the end). The problem is probably due to some of your records having invalid dates in them. Try using ISDATE() to check that you have a valid date. eg:

DTSDestination("INVOICE_DATE") = case when isdate(DTSSource("Col004")) = 1 THEN DTSSource("Col004") ELSE null END

Instead of Null you could have a default date if you want (eg '20000101')

Regards

Peter

fmantao SSC-Addicted Points: 406 More actions · Answer 7

I tried all the different permutations and still could not tweak the transformation using CDATE. But as per your suggestions, I downloaded the data per se without any conversion and then wrote a package to convert the data and transfer them to our database tables.

Just wanted to thank everyone who took the time to help me with this problem.

Regards

Eric Pedrotty Newbie Points: 5 More actions · Answer 8

Sorry to post this so late, but I had the same problem and just solved it.

My source file includes strings with date data formatted like:

"YYYYMMDD" and not all rows have values in the column.

Try this:

If Len(DTSSource("Col003")) > 1 Then

If IsDate(CDate(Left(DTSSource("Col003"),4)+"/"+Mid(DTSSource("Col003"),5,2)+"/"+Right(DTSSource("Col003"),2))) Then

DTSDestination("DateOfService") = CDate(Left(DTSSource("Col003"),4)+"/"+Mid(DTSSource("Col003"),5,2)+"/"+Right(DTSSource("Col003"),2))

Else

DTSDestination("DateOfService") = NULL

End If

Else

DTSDestination("DateOfService") = NULL

End If

The first If gets rid of a type mismatch error on the IsDate function then test and convert the source field if the result is a date.

I'm sure others have already figured this out, but I couldn't find it...