Converting the given minutes to hh:mm:ss format

Question

Here's my database structure (simplified):

if db_id('MovieDB') is null
    create database MovieDB;

go

use MovieDB;

go

if object_id(N'Movies', N'U') is null
create table Movies
(
    MovieID         bigint          not null    identity    primary key,
    Duration        time(7)         not null,
);

insert into Movies values
(format(dateadd(minute, 142, 0), N'hh:mm:ss', 'en-US'));

I inserted the duration as minutes and I need it to be converted to hh:mm:ss format. However, with the execution of this I get 02:22:00.0000000.

How can I format the date to get just 02:22:00?

Answer 1

You could convert to a time:

select convert(time, dateadd(minute, 142, 0))

Or, if you want the value as a string, you can use the old-fashioned convert():

select convert(varchar(8), dateadd(minute, 142, 0), 108)

But I'm confused. You are storing the value as a time with 7 digits of fractional seconds. Then you are complaining about seeing the fractional seconds.

Fractional seconds probably don't make a difference for movie times, so why not just use:

Duration        time(0)         not null,