CODEWITHSUNDEEP
sql-server-2008
tfp
tfp

Reputation: 91

SQL Server convert datetime to int in query

I have a field that contains time in seconds after midnight, however SQL Server will only display it as datetime yyyy-mm-dd hh.ss.xxx. I am trying to query this and convert the data to seconds after midnight, the way it should be, e.g. 01:00 would be 3600.

I have tried SELECT CONVERT(int, timefield) but the result is a column named (No column name) and values are -1 or -2.

What am I doing wrong here?

Thanks for any help!

Upvotes: 0

Views: 4371

Answers (2)

Dave
Dave

Reputation: 11879

Use the function DATEDIFF:

SELECT DATEDIFF(SECOND, 0, '12:00:00')

From http://www.eggheadcafe.com/software/aspnet/35057980/mssql-timetosec.aspx

Upvotes: 3

Emaad Ali
Emaad Ali

Reputation: 1501

You want something like this 

  SELECT [Total Seconds] = 
  (DATEPART(hh, GETDATE()) * 3600) + 
  (DATEPART(mi, GETDATE()) * 60) + DATEPART(ss, GETDATE())

Hope my answer help you to solve your problem.

Upvotes: 1

Related Questions