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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy