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?

`SELECT TOP (1000) [MovieID] ,[Duration] FROM [MovieDB].[dbo].[Movies]` — user3132457, Mar 16 '19 at 15:00

Gordon Linoff · Accepted Answer · 2019-03-16T15:03:59.507

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,

edited Mar 16 '19 at 15:03

answered Mar 16 '19 at 14:32

Gordon Linoff

1,242,037
58
646
786

yeah actually it shouldn't be `time(7)`, but after changing to `time` or `time(0)` i still get the 7 zeros – user3132457 Mar 16 '19 at 14:43
@user3132457 . . . You can convert the value to a string (as in the first example) to get what you exactly want. – Gordon Linoff Mar 16 '19 at 14:57
it's still the same – user3132457 Mar 16 '19 at 15:00
1

@user3132457 . . . It is impossible to get fractional seconds with `convert(varchar(8), duration, 108)`. You need to do this when you *select* the data, not when you insert it. – Gordon Linoff Mar 16 '19 at 15:04

Converting the given minutes to hh:mm:ss format

1 Answers1