Saturday, September 22, 2012 3:19 PMI've written a VB.NET program that reads a comma delimited text file into an SQL Server table. I have more than two hundred such files with literally hundreds of thousands of rows of data. Unfortunately, the existing time data is in the wrong format (e.g. 0350 instead of 03:50).
I haven't used TSQL in a while but I am almost sure it can do that. I imagine I would have to read each records IncorrectTime field's value, assign the two left characters in temp1 variable, assign the two right characters in temp2 variable, and then assign to temp3 = temp1 & ":" & temp2. Or something along those lines.
Can anyone offer me some guidance? Is this something that can be done using TSQL or would I have do it all in VB and then run a query to update each record?
Saturday, September 22, 2012 3:24 PMUse SUBSTRING() function, and/or LEFT(), RIGHT() to concatenate the left two chars, ":" and the right two chars to solve the purpose.
Welcome to MSDN Forums.
Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.
You Value Community's efforts and Community values your faith.
- Arun Kumar Allu
Saturday, September 22, 2012 3:27 PMModerator
Probably just a simple UPDATE statement in T-SQL.
The SQL language has built-in implicit looping, so you don't have to program looping.
If you post table structure, we can assist you faster.
Saturday, September 22, 2012 5:15 PM
Yes, you can do it in T-SQL, but wouldn't it be better to do this in VB .Net while you read the data?
Erland Sommarskog, SQL Server MVP, email@example.com
- Proposed As Answer by Naomi NMicrosoft Community Contributor, Moderator Sunday, September 23, 2012 4:11 AM
- Unproposed As Answer by Kalman TothMicrosoft Community Contributor, Moderator Thursday, September 27, 2012 2:42 PM
Sunday, September 23, 2012 4:10 AMModeratorHi Antonic,
Is the time data stored in a VARCHAR column, or in a datetime field? If in a VARCHAR column, do the following
SET <time_column> = LEFT(<time_column>,2) + ':' + RIGHT(<time_column>,2);
This will update your entire table.
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
- Marked As Answer by Antonic217 Friday, September 28, 2012 2:18 PM
Friday, September 28, 2012 2:18 PMthat helps. thanks iric
Friday, September 28, 2012 2:40 PM
We have a TIME data type, so add a new column of that type, build a string (remember the seconds!) and cast it.
ALTER TABLE Foobar
ADD COLUMN good_time TIME;
SET good_time = CAST (LEFT(bad_time,2) + ':' + RIGHT(bad_time,2)+':' + '00' AS TIME) ;
Might want to make good_time NOT NULL after you load it.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL