How do I convert time into an integer in SQL Server

Question

I have a date column and a time column that are integers

I converted the date portion like this

select convert(int, convert(varchar(10), getdate(), 112))

I thought I could do the same with this query that gives the time in HH:mm:ss

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)

How do I convert just the time into an integer?

Diadromous

Do a datediff in seconds? – Arta

Do you mean seconds since midnight? – Hanhhank

Answer 1

Assuming you are looking for the "time" analogy to the "date" portion of your code which takes YYYYMMDD and turns it into an INT, you can:

1. start with the HH:mm:ss format given by the style number 108
2. remove the colons to get that string into HHmmss
3. then convert that to INT

For example:

SELECT REPLACE(
               CONVERT(VARCHAR(8), GETDATE(), 108),
               ':',
               ''
              ) AS [StringVersion],

       CONVERT(INT, REPLACE(
                       CONVERT(VARCHAR(8), GETDATE(), 108),
                       ':',
                       ''
                    )
              ) AS [IntVersion];

Anamorphoscope

Answer 2

This should convert your time into an integer representing seconds from midnight.

SELECT (DATEPART(hour, Col1) * 3600) + (DATEPART(minute, Col1) * 60) + DATEPART(second, Col1) as SecondsFromMidnight FROM T1;

Purveyor

Answer 3

You can use the differece between midnight and the time of the day. For example, using getdate(), you can know the percentage of the time of the day with this query:

select convert(float,getdate()-convert(date,getdate()))

You can then convert this number to seconds

select convert(int,86400 * convert(float,getdate()-convert(date,getdate())))

You'll get the number of seconds from midnight

Tracheo

Answer 4

I think this is easier to understand when using with a SQL Update statement.

UPDATE dbo.MyTable SET TIME_AS_INT = CAST(REPLACE(CAST(CONVERT(Time(0), GETDATE()) AS varchar),':','') AS INT)

To add/subtract time from the result before converting use dateadd()

SELECT CAST(REPLACE(CAST(CONVERT(Time(0), dateadd(MINUTE, 1, getdate())) AS varchar),':','') AS INT)

Theaterintheround