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.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in SQL Server
4 comments on “T-SQL: Convert Seconds to Datetime / Get Total Seconds from Datetime Field
  1. Leandro says:

    and if i need pass from seconds (xxxx) to min:sec (mm:ss)
    Like:
    156 sec -> 2:36

  2. Artur says:

    Interesting article, but this might be a more efficient (and shorter) way of doing it:

    print right(convert(varchar,dateadd(millisecond,7654321,’01/01/1901 0:0:0.000′),121),12)

    7654321 milliseconds converts to 02:07:34.320
    Also, using ODBC standard provides more “standardized” time format (. instead of : before milliseconds) and it assures that the output length is constand, even if milliseconds are 000.

    For Leonardo:
    print right(convert(varchar,dateadd(second,156,’01/01/1901 0:0:0.000′),121),12)

    BTW, nice curl :)

    Artur

  3. P-Sa-Cake says:

    Thanks Arthur. RDLC report dynamically converts it and was curious to find out how

Leave a Reply

Your email address will not be published. Required fields are marked *

*