Take the time from the datetime and convert it into seconds?
Solution 1
Use DATEPART
:
(DATEPART(HOUR, GETDATE()) * 3600) +
(DATEPART(MINUTE, GETDATE()) * 60) +
(DATEPART(SECOND, GETDATE()))
Solution 2
Just my 2 cents ...another way of doing this
Edit: Added method for SQL Server 2005 (Thank you 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())
Bill Blankenship
I like to code. Is it wierd to miss the sound of a modem dialing up to the internet???
Updated on July 05, 2022Comments
-
Bill Blankenship almost 2 years
I am running SQL Server 2005. Technically I know how to take the time from a tsql datetime.
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond
The problem is that I have a datetime field and I need to essentially grab the time portion convert that to an integer specifically seconds. Then I need to do a bunch of arithmetic on this integer that I won't discuss here. I have searched on stackoverflow and haven't found a question that is specific to this question. Any ideas? I am really looking for best practices here, I am worried about creating a udf for this specific purpose as it completely throws the query optimizer out the window.
I have seen this webpage so please don't paste this. :
http://forums.devx.com/showthread.php?171561-TSQL-Converting-HH-MM-SS-to-seconds