Changing table data

Question

Changing table data

jamesstirling01

Mr or Mrs. 500

Points: 596
More actions
April 17, 2019 at 1:11 pm

#3425552
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
- This topic was modified 5 years, 7 months ago by jamesstirling01.

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

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

rVadim Hall of Fame Points: 3969 More actions · Answer 1

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: 596 More actions · Answer 2

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: 596 More actions · Answer 3

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

Dinosaur SSC Enthusiast Points: 123 More actions · Answer 4

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: 596 More actions · Answer 5

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.

This reply was modified 5 years, 7 months ago by jamesstirling01.

Dinosaur SSC Enthusiast Points: 123 More actions · Answer 6

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: 596 More actions · Answer 7

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: 596 More actions · Answer 8

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 More actions · Answer 9

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

FROM #TimeReported;

jamesstirling01 Mr or Mrs. 500 Points: 596 More actions · Answer 10

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 More actions · Answer 11

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: 596 More actions · Answer 12

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: 123 More actions · Answer 13

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 SSC Eights! Points: 804 More actions · Answer 14

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.