none
please help RRS feed

  • Question

  • I'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:19 PM

Answers

  • Hi Antonic,

    Is the time data stored in a VARCHAR column, or in a datetime field? If in a VARCHAR column, do the following

    UPDATE TABLE
    SET <time_column> = LEFT(<time_column>,2) + ':' + RIGHT(<time_column>,2);
    GO

    This will update your entire table.


    Best Regards,
    Iric
    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
    Sunday, September 23, 2012 4:10 AM
    Moderator

All replies

  • Use 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:24 PM
  • 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.

    UPDATE article:

    http://www.sqlusa.com/articles2005/sqlupdate/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Saturday, September 22, 2012 3:27 PM
    Moderator
  • 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, esquel@sommarskog.se
    Saturday, September 22, 2012 5:15 PM
  • Hi Antonic,

    Is the time data stored in a VARCHAR column, or in a datetime field? If in a VARCHAR column, do the following

    UPDATE TABLE
    SET <time_column> = LEFT(<time_column>,2) + ':' + RIGHT(<time_column>,2);
    GO

    This will update your entire table.


    Best Regards,
    Iric
    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
    Sunday, September 23, 2012 4:10 AM
    Moderator
  • that helps. thanks iric
    Friday, September 28, 2012 2:18 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;

    UPDATE Foobar
    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

    Friday, September 28, 2012 2:40 PM