converting a string or integer to a datatime

Question

converting a string or integer to a datatime

alen teplitsky

SSC-Dedicated

Points: 30014
More actions
December 27, 2006 at 6:54 am

#115668

Maybe someone works for a telecom here and can help me. We download a bunch of data every month to calculate taxes. We download the text files and import them into a taxation db. Been doing it for years.
when the process was originally made up we imported some data as a string and then changed the whole column to smalldatetime. I'm trying to recreate the import in SSIS rather than just importing the DTS packages and I want to automate the conversion.
Here is what the data looks like:
0600000000 0.030000 0 0.000.0000001998010100000FEDERAL EXCISE TAX 0.0000000.000000 0.000000 0.0000 10000
0600000000 0.060000 0 0.000.0000002002010110101STATE SALES TAX 0.0600000.000000 0.000000 0.0000 10000
0600000000 0.005000 0 0.000.0000002006110110600CA EMECY TEL. USERS SURCHARGE 0.0000000.000000 0.000000 0.0000 10000
0600000000 0.001100 0 0.000.0000001996070110800CA P.U.C. FEE 0.0000000.000000 0.000000 0.0000 00101
0600000000 0.000500 0 0.000.0000002006080110900CA TELCOM RELAY SYS. SURCHARGE0.0000000.000000 0.000000 0.0000 10100
0600000000 0.020000 0 0.000.0000002006010111700CA HIGH COST FUND(B) SURCHARGE0.0000000.000000 0.000000 0.0000 10100
Right before the federal on the second line is this
19980101
that's an example of what my date looks like in the file. The above is 22 columns worth of data.
the data conversion and other transforms all fail. Is there a way to do it or should I just automate some SQL code to convert the whole column to smalldatetime once it's imported?

Viewing 3 posts - 1 through 2 (of 2 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

Your date starts at the same position in all columns so you can use SUBSTRING and CONVERT functions to get the date data...

declare @string Varchar(1000)

select @string = '0600000000 0.030000 0 0.000.0000001998010100000FEDERAL EXCISE TAX 0.0000000.000000 0.000000 0.0000 10000'

select convert (datetime,substring (@string, 35, 8))

MohammedU
Microsoft SQL Server MVP

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

i set it up to execute sql for an alter table task

i change the smalldatetime to varchar(8) before the import then change the column back to smalldatetime after all the data is imported

seems to work OK, going to hand it off to QA after I finish