Find time from a date and time and convert it in seconds?

Question

I am running SQL Server 2005. Technically, I know how to take the time from tsql datetime.

CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond 

The problem is that I have a datetime field and I need to essentially capture some of the time in order to convert it to integers in seconds. Then I need to do a bunch of arithmetic on this whole, which I will not talk about here. I searched on stackoverflow and did not find a question that is specific to this question. Any ideas? I am really looking for best practices here, I am worried about creating udf for this specific purpose, as it completely displays the query optimizer from the window.

I saw this web page, so do not embed it.

http://forums.devx.com/showthread.php?171561-TSQL-Converting-HH-MM-SS-to-seconds

Answer 1

Use DATEPART :

 (DATEPART(HOUR, GETDATE()) * 3600) + (DATEPART(MINUTE, GETDATE()) * 60) + (DATEPART(SECOND, GETDATE())) 

Answer 2

Only my 2 cents ... another way to do it

Edit: Added method for SQL Server 2005 (thanks Michael)

for SQL Server 2008

 SELECT DATEDIFF(SECOND, CONVERT(date, GETDATE()), GETDATE()) 

for SQL Server 2005+

 SELECT DATEDIFF(SECOND, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), GETDATE()) 

Answer 3

Functions You Want - DatePart

 Declare @d DateTime Select @d = GetDate() Select (DatePart(HOUR, @d) * 3600) + (DatePart(MINUTE, @d) * 60) + DatePart(SECOND, @d)