T-SQL: Convert Seconds to Datetime / Get Total Seconds from Datetime Field

For my final project in my database class at USF, I chose to create a database for a small record label looking to sell albums online as well as provide artist bios, tour information and news. The database, which was scaled down version of a project I worked on in the past, looked something like this:

The professor asked us to compose a few sample queries and, while looking at the songs table, I wondered if there was a way that I could use the information in the length column to determine how long each album ran. The song length column was created as datatype datetime for this very reason. Since SQL Server does't support just a time column, I used the dummy 1900-01-01 date when INSERTING my values. A typical song length would looks like this: 1900-01-01 00:02:11.000.

So I wanted to find the full time length of an album without text parsing to look for ":" and so on. Ultimately, I found this to be possible. Here's the query I came up with:

SELECT CONVERT(VARCHAR,DATEADD(ss,SUM(DATEPART(HOUR, length)*3600 + DATEPART(MINUTE,length)*60 + DATEPART(SECOND,length)),0),114) AS AlbumLength FROM AlbumsTracks Where AlbumID = 1

What you see going on above is the following: 1. Using the DATEPART function to break down the song length into seconds. This is done by grabbing the various parts and multiplying them by 60 for minute or 3600 for hour. 2. Using DATEADD function to add those seconds to the date of "0" (1900-01-01). 3. Using the CONVERT function's ability to change datetime into various formats (kinda like VBScript's FORMATDATETIME), I went with style 114 or hh:mi:ss:mmm (24h))

The above query produced the result: 00:56:02:000. That's a 56 minute and 2 second running time. For more information on CONVERT's ability to manipulate datetime, check out SQL Server Books Online.