var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Replace data using sql
I have a database of about 300,000 records.
The records were imported from a csv file.
One of the fields is duration.
The data in duration are like ths:
1 second: 0:01
26 minutes: 26:00
If i put the format of the field as time, the data are messed up.
0:01 becomes 1 minute.
26:00 becomes 1 day 2 hours.
I currently have duration as text.
How can i use sql or visual basic to replace all the data so that they can have the format "00:00:00"?
(0:01 becomes 00:00:01, 26:00 becomes 00:26:00)
I need the duration in time format in order to be able to make sum calculations.
I will be doing the same calculations every month so i need the above procedure to be able to execute it every time i need to.
Thank you in advace
This smells easy. I'd try:
CREATE TABLE tRousoug (
foo VARCHAR(8) NOT NULL
INSERT INTO tRousoug (foo)
SELECT '0:01' UNION ALL SELECT '26:00'
SELECT Left('00:00:00', 8 - Len(foo)) + foo
My Table is named "June 2004" and the field containing the data "Duration".
Can you rewrite the code using the above information since i am not able to make it work?
SELECT Left('00:00:00', 8 - Len(Duration)) + Duration
FROM [June 2004]
Ok. That seems to work. But ut does not replace the data. It only creates a query with the changes. The field data in the table remain the same.
Write an UPDATE statement.
Perhaps you should at least bother to open a book or try to look at the documentation before posting to forums asking people to do your work for you. I'm sorry for being so harsh but it really doesn't seem like you're trying very hard. If I am wrong and you did try with the documentation please accept my apologies.
dagjo, you sound quite experienced, im currently on some microsoft courses, could you reccommend any good books at all?
Oh, and by the way, the solution is
UPDATE [June 2004]
SET Duration = Left('00:00:00', 8 - Len(Duration)) + Duration
Sorry about this, end of a working day, i'm a little bit tired. I think i need some time off and a revision of my books.
sorry, but I'm not primarily a db developer and thus I've got pretty limited knowledge of books and certainly not enough to tell which ones are good and which ones aren't.
I can and do recommend to use the help files that ship with SQL Server. As a general rule, I recommend trying to look it up in the doc before posting to forums.
on my querying using transact sql course they were describing how to use the help effectively as I never had a clue when I used it... its surprising just how much help the help is, once you know how to read it properly of course.
Thanks all the same Dag!