How does one convert times in SQL to a desired format?

SQL Server Administration
1

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.

Any assistance would be appreciated.

2

hi

hope this helps

declare @var time 

set @var = '11:27:47.973'

select @var ,CONVERT(VARCHAR(5), @var, 108)

@var = 11:27:47.9730000

CONVERT(VARCHAR(5), @var, 108) = 11:27

3

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:

22:10:00.0000000 15:54:00.0000000 19:40:00.0000000 00:52:00.0000000 18:03:00.0000000 17:50:00.0000000 16:30:00.0000000

What syntax can I apply to the entire column to convert all of the dates to hh:mm format (there are 23,568 entries in total)?

4

hi what abt this ?

this assumes the seconds part will always be like this
:00.0000000

declare @var varchar(2000) = '22:10:00.0000000 15:54:00.0000000 19:40:00.0000000 00:52:00.0000000'

select @var as var , replace(@var,':00.0000000','') as replace_var

image

5

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
6

are you saying all of this data is in one column? if so, why?!?

declare @moon table(timeofmylife varchar(max))
insert into @moon
select '22:10:00.0000000 15:54:00.0000000 19:40:00.0000000 00:52:00.0000000'

;with wow
as
(
select * , FORMAT(cast(x.value as time), N'hh\:mm') scary
  from @moon
  cross apply string_split(timeofmylife, ' ') x
)
select STRING_AGG(scary, ' ') from wow
7

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.

Declare @myTime int = 1430;

Select hh = @myTime / 60
     , mm = @myTime % 60
     , myTime = concat(right('0' + (@myTime / 60), 2), ':', right('0' + (@myTime % 60), 2));

Instead of going through all that - use time(0) and convert the output to whatever format you desire.

1 Like
8

Thank you for the helpful response.

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.

9

Create a new thread with that question