Changing table data

  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    Hi,

    I have a row in a table call 'Time Reported' with 29602 rows the data for the table looks like the below:

    1899-12-30 09:12:32.000 each row has a different time but keeps the 1899-12-30 & .000 at the end.

    I beleive it is formatted like this because it was pulled from a old Access DB. i would like a query to remove the following.

    1899-12-30 & the .000 is there anyone that can assist with me with doing this.

    Cheers James

  • rVadim

    Hall of Fame

    Points: 3969

    DECLARE @D DATETIME = '1899-12-30 09:12:32.000'
    SELECT @D, CAST(@D AS TIME), CAST(CAST(@D AS TIME) AS CHAR(8))

    --Vadim R.

  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    Thanks rVadim but the 'Time Reported' is a different time in each row for example:

    1899-12-30 09:12:32.000 -Row1

    1899-12-30 09:13:12.000 -Row2

    1899-12-30 09:21:39.000 -Row3

    1899-12-30 09:24:04.000 -Row4

     

  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    To hopefully assist further the table is called Job info$ the column is called Time Reported

  • Dinosaur

    SSC Enthusiast

    Points: 122

    Try something like below.  Replace the temp table and column names with you table and columns names) this:

    CREATE TABLE [#TimeReported]
    (
    [ID] INT NOT NULL IDENTITY( 1 , 1 ) ,
    [DateTimeCol] DATETIME NOT NULL
    ) ;


    INSERT INTO [#TimeReported]
    ( [DateTimeCol] )
    VALUES
    ( '1899-12-30 09:12:32.000' ) ,
    ( '1899-12-30 09:13:32.000' ) ,
    ( '1899-12-30 09:21:32.000' ) ,
    ( '1899-12-30 09:24:32.000' ) ;

    SELECT [DateTimeCol] ,
    [TimeOnly] = CONVERT( TIME , [DateTimeCol] )
    FROM [#TimeReported] ;

    DROP TABLE [#TimeReported] ;
  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    Hi Grasshopper,

    I already have a table called job info$ and in column Time Reported i have 29602 rows i need to change all of them.

    Are you suggesting creating a new table converting them to the correct format and then copying them back?

    Here is a image of the my column i am on about. I want to delete the 1899-12-30 & .000 from every row.

    https://www.sqlservercentral.com/wp-content/uploads/hm_bbpui/3427326/mhmwh95vq0bhr2ijedwtjaaq5elq9gj4.JPG

  • Dinosaur

    SSC Enthusiast

    Points: 122

    No, the temp table is for illustration only.  The query is something like:

    SELECT [Time Reported] ,
    [TimeOnly] = CONVERT( TIME , [Time Reported] )
    FROM [Job info$] ;
  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    Hi morenoj5958 81627

    That worked to a point :0) but i would like to change the values in time reported to those generated and i would like also to remove the trailing .0000000 populated once the query runs  for example 09:12:32.0000000 to be 09:12:32

    Thanks for the assistance.

    Cheers James

  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    I have had a go at righting my own query but having a few issues. As per the norm thanks for everyones assistance so far.

    /*Create new colunm TimeOnly */
    ALTER TABLE ['Job info$']
    ADD [TimeOnly] DATETIME NOT NULL
    /*Convert [Time Reported] to correct format*/
    SELECT ['Job info$'].[Time Reported] ,
    [TimeOnly] = CONVERT( TIME , ['Job info$'].[Time Reported] )
    FROM ['Job info$'] ;
    GO
    /*Copy [TimeOnly] to [TimeReported]*/
    UPDATE ['Job info$']
    SET ['Job info$'].[TimeOnly]=[Time Reported]
    GO
    /*Remove .0000000 from [Time Reported]*/

    The copy TimeOnly is showing a error but i think its something to do with the column is not there so it pulls the error.

    Also i am struggling with the last part cleaning the data so its a clean time like 12:48:32 not 12:48:32.0000000

    I havent had much input on SQL querys only the very basic's so if it wont work tell me :]

    Cheers James

  • Cath Trimble

    SSCarpal Tunnel

    Points: 4201

    SELECT FORMAT(DateTimeCol, N'HH\:mm')

    FROM #TimeReported;

  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    Hi Cath Trimble,

    SELECT FORMAT(DateTimeCol, N’HH\:mm’)

    FROM #TimeReported;

    Errors on DateTimeCol & N' "Invalid coloum changed DateTimeCol to ['Job info$'].[Time Reported] error could not be bound.

    Also changed #TimeReported to ['Job info$'].[Time Reported]

    Cheers James

  • Cath Trimble

    SSCarpal Tunnel

    Points: 4201

    CREATE TABLE [Job info$]

    (

    [Time Reported] DATETIME

    )

    INSERT [Job info$] ([Time reported])

    VALUES

    ( '1899-12-30 09:12:32.000' ) ,

    ( '1899-12-30 09:13:32.000' ) ,

    ( '1899-12-30 12:21:32.000' ) ,

    ( '1899-12-30 19:24:32.000' ) ;

    SELECT FORMAT([Time Reported], N'HH\:mm')

    FROM [Job info$] ;

    DROP TABLE [Job info$];

  • jamesstirling01

    Mr or Mrs. 500

    Points: 562

    I am really CONFUSSED why do i have to create a table i already have a table called [job Info$] with a column called [Time Reported] all i want to do is CHANGE all the values from HH:MM:SS.0000  to HH:MM:SS if possible in that column.

  • Dinosaur

    SSC Enthusiast

    Points: 122

    The following SQL query retrieves only the time as you'd like:

    SELECT [TimeOnly] = FORMAT( [DateTimeCol] , N'hh\:mm\:ss' )
    FROM [Job info$] ;

    Now, if you need to persist the Time in the table, you'll need to alter the table to add a column to hold the time value, and update the table.

  • michael.cole 47030

    Right there with Babe

    Points: 732

    jamesstirling01 wrote:

    I am really CONFUSSED why do i have to create a table i already have a table called [job Info$] with a column called [Time Reported] all i want to do is CHANGE all the values from HH:MM:SS.0000  to HH:MM:SS if possible in that column.

    Because you cannot just change the value - the value is correct.  You may change the data type that it is stored as, or you may format the output that is returned.  Figure out what you are trying to do first, and then understand how a database works.  Sorry to be so cruel but this shows a fundamental lack of understanding of how a database works.

    1.0 = 1 - the values are correct.  You may either store the values without the decimal point, or you may format the output without the decimal point, but the value itself does not change.

     

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply