I am in the process of cleaning data in a table containing a column of times which I have already converted to the 'time' datatype. I used the following code to perform the conversion:
alter table PortfolioProjects..NYPD_shootings alter column OCCUR_TIME time
The conversion worked; however, the converted times are in 'hh:mm:ss.0000000' format, whereas I wish to have them in 'hh:mm' format. The converted datatype is listed as time(7). I have searched the internet far and wide for an answer to this question and come up with nothing.
Thank you for your response; however, this approach would give me the current time, which is not what I am trying to convert. I am seeking to change the format of a series of different times contained within a column of a dataset. Part of the column is shown below:
please provide your data as follows for next time?
declare @moo table(milk time)
insert into @moo
select '22:10:00.0000000' union
select '15:54:00.0000000' union
select '19:40:00.0000000' union
select '00:52:00.0000000' union
select '18:03:00.0000000' union
select '17:50:00.0000000' union
select '16:30:00.0000000'
select FORMAT(milk, N'hh\:mm')
from @Moose
The time data type is not 'stored' in the database in a particular format. If you want a specific format on output you can either convert to a string - or convert to time(precision).
If you don't want the data to be stored with a precision of 7 - change the column and specify the desired precision.
The lowest available precision is 0 - which stores the data with an accuracy to the second. That will give you HH:MM:SS time.
If you really want to store a time with a precision to the minute - you would need something other than a time or datetime data type. For that you could convert the time the number of minutes since midnight and store that value - then convert that value to HH:MM when selected.
I have one other question as well: how can I add/drop columns from a table in SQL without getting an error message when I rerun the code afterwards? When I've dropped a column, it tells me the column is invalid and when I've added one, it tells me the column already exists.