August 21, 2004 at 12:31 pm
Hello All,
Situation:
Current:
I have a table with approx 450,000 rows and 88 columns. Only half the columns are in use (rest are for future use). I have no control of how the application spits out the data. I take the data and insert it into the current table (more or less of a storage table).
There are 3 columns (starttime, releasetime, & endtime) with a string value repesenting a date time value:
20040122122210.123/yyyyMMddhhmmss.fff
the dates/times ranges from JAN 2004 to present.
I'm loading anywhere from 10,000 to 30,000 new rows daily.
Whats needed:
I need to convert the current value (see above) to a DATETIME type for data munipulation (subtraction, querying)
declare @Source char(18)
SET @Source = '20040823101156.000'
select convert (datetime,
SUBSTRING(@Source, 5, 2) + '/' + SUBSTRING (@Source, 7, 2) + '/' +LEFT (@Source, 4) + ' ' -- date
+ substring (@Source, 9, 2) + ':' + substring (@Source, 11, 2) + ':' + right (@Source, 6)) -- time
The script about works well for a single value.
What I've Tried
I've tried to use DTS for the conversation into a table with the columns in question set to DATETIME data type. When I run the test on the transformation it runs for approx 200 ( out of 9000) rows and stops with a error 'to many error, can't parse source data'. When I go check the test data it look good. The transform goes from
20040823101156.000 to 2004-08-23 10:11:56.000
However it stops
It would be good if I could convert the current table.
Any help with this would be greatly appreaciated
Shug
August 22, 2004 at 11:05 pm
Have you tried adding a column to the existing table, with a datetime data type, and using an update statement to convert the exisitng data?
UPDATE myTable
SET MyNewDTCol = convert (datetime,
SUBSTRING(@Source, 5, 2) + '/' + SUBSTRING (@Source, 7, 2) + '/' +LEFT (@Source, 4) + ' ' -- date
+ substring (@Source, 9, 2) + ':' + substring (@Source, 11, 2) + ':' + right (@Source, 6)) -- time
You may also want to find those rows that don't convert nicely with the ISDATE() function
Select MyBadDateCol
From myTable
Where ISDATE(SUBSTRING(@Source, 5, 2) + '/' + SUBSTRING (@Source, 7, 2) + '/' +LEFT (@Source, 4) + ' ' -- date
+ substring (@Source, 9, 2) + ':' + substring (@Source, 11, 2) + ':' + right (@Source, 6))) = 0
Julian Kuiters
juliankuiters.id.au
August 23, 2004 at 11:26 am
What are the datatypes of your starttime, releasetime, & endtime columns in your intermediate target table?
Corie Curcillo
MCT, MCDBA, MCSD
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply