Link to home
Create AccountLog in
Avatar of Wayne Barron
Wayne BarronFlag for United States of America

asked on

SQL Server 2019 - Convert Time(7) value to second to display to webpage

Hello All.
SQL Server 2019 | ASP Classic


Convert (Data Type) Time(7) to seconds.

I've tried several functions found through other forums, but they are all for DateTime and do not work with Time(7).
Reason for using Time(7)

The Column is used to store the time of songs.

Example: Song time: 3:13 

The Column displays: 03:13:00

I need to display: 193
The seconds will be used in a Property tag
<meta property="music:durationcontent="193">

If using a different Data Type is preferred in this case, that will work.

As long as values do not change too much on my pages.

Example:

Displaying song time: 3:13

Showing the total time of all songs on the album: 45.35


Thanks.
Wayne

Avatar of Noah
Noah
Flag of Singapore image

Hi!

You can use the DATEDIFF function along with some calculations. Here is a code you can incorporate. I have included notes in the code for your reference.

-- Assuming you have a table called 'Songs' with a column 'SongTime' of type TIME(7)

-- Calculate the total seconds for a specific song
DECLARE @songTime TIME(7) = '03:13:00';
DECLARE @totalSeconds INT;

-- Extract hours, minutes, and seconds from the time value
DECLARE @hours INT, @minutes INT, @seconds INT;
SELECT @hours = DATEPART(HOUR, @songTime),
       @minutes = DATEPART(MINUTE, @songTime),
       @seconds = DATEPART(SECOND, @songTime);

-- Calculate the total seconds
SET @totalSeconds = (@hours * 3600) + (@minutes * 60) + @seconds;

-- Display the total seconds
SELECT @totalSeconds AS TotalSeconds;

Open in new window

Avatar of Wayne Barron

ASKER

That gives the same results as the other codes I've tried.
11580
I need 193
How can I get the 193 from 11580?
SELECT DATEDIFF(MINUTE, CAST('00:00:00' AS TIME(7)), [song_time]) AS seconds
FROM x;

Open in new window

but 193 represents MINUTES because 03:13:00 is 3 hours, 13 minutes and zero seconds
That is a VERY long song


I have made a slight tweak.

-- Assuming you have a table called 'Songs' with a column 'SongTime' of type TIME(7)

-- Calculate the total seconds for a specific song
DECLARE @songTime TIME(7) = '03:13:00';
DECLARE @totalSeconds INT;

-- Extract minutes and seconds from the time value
DECLARE @minutes INT, @seconds INT;
SELECT @minutes = DATEPART(MINUTE, @songTime),
       @seconds = DATEPART(SECOND, @songTime);

-- Calculate the total seconds
SET @totalSeconds = (@minutes * 60) + @seconds;

-- Display the total seconds
SELECT @totalSeconds AS TotalSeconds;

Open in new window

Paul.
When the data is inserted into the table, it is done so as
(ASP.NET Code for stripping metadata from MP3 Files)

Dim getDuration As String = theDuration.ToString("mm\:ss")

And it is then inserted into the table as 3:13
So, the seconds of that would be 193

If it is doing it as hours:minutes:seconds
That is not the intended result I am after.
How can I get it to submit as it is supposed to be?

Noah, your code is giving 780

It seems that what I thought was minutes/seconds.
As Paul has pointed out, is infact, Hours:Minutes:Seconds
So, this is not going to work until this part is fixed.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I changed the format for each value in the database itself from
03:13:00
to
00:03:13
For testing, and it WORKED!

And then ran this SQL and got the intended results.
select total_seconds =DATEDIFF(second,0,cast(TrackTime as datetime)) from Tracks where TrackID=1

Open in new window

THANK YOU for pointing out what needed to be done.

Now, time to add the beginning 00: to the insert code, as we are good to go.

Thank you, Paul.
Have a rockin' rest of the week.

Wayne
I just added it into the script and ran it, and it worked on the first run.

"00:" & getDuration

Open in new window

Sometimes it just takes a new set of eyes :-) 

Happens to me frequently.

That it does.
Thanks, Paul.