Return current Time as integer

Question

Return current Time as integer

DerbyNeal

Hall of Fame

Points: 3854
More actions
February 2, 2011 at 2:50 am

#231452

Hi,
I have a table 'Batch' that contains a Date column (2011-02-01 00:00:00.000) and a Tme column as an integer (67737).
I can convert the 'Time' field to current hh:mm:ss format using the following code:
CONVERT(NVARCHAR(8), DATEADD(hh, 0, GETDATE()), 108)
But how do I convert the current time to an integer (i.e. Just the time portion of the current date/time)?
Thanks in advance,

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

I assume the integer should represent the number of seconds since midnight. There may be more elegant ways, but this should do it:-

SELECT DATEDIFF(S, CONVERT(DATETIME,CONVERT(CHAR(11),GETDATE())), GETDATE())

Lowell SSC Guru Points: 323546 More actions · Answer 2

the value looks like the # of seconds, as Ian said; so you could add the seconds to the date component to get the read date and time, right?

and pull them out with what Ian posted...but why are you converting the values back and forth from datetime to integers and back again? why not leave them as datetimes?

--returns 2011-02-01 18:48:57.000'

select dateadd(ss,67737,'2011-02-01 00:00:00.000')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

john.arnott SSChampion Points: 11882 More actions · Answer 3

Building on Ian's suggestion, it should be more efficient to use the DateDiff() and DateAdd() functions rather than Convert() to isolate the time portion of a DateTime, then find the equivalent number of seconds with DateDiff(). This code shows that the "time-only" in a DateTime is really the time on the base date of 1900-01-01, then shows the datediff between that and the base date (yes, it's legitimate to use "0" there -- it's not a hack) as the number of seconds.

declare @mydttm datetime

set @mydttm = '1857/02/12 13:17'

Select @mydttm - DateAdd(dd, Datediff(dd, 0, @mydttm),0) as justtime

,DateDiff(s,0,@mydttm - DateAdd(dd, Datediff(dd, 0, @mydttm),0)) as MySeconds

BTW, why the "DateAdd" in the original code to show the time only? Seems that the Convert would accept the getDate() directly.

Select

CONVERT(NVARCHAR(8), DATEADD(hh, 0, GETDATE()), 108)

,CONVERT(NVARCHAR(8), GETDATE(), 108)

But again, the DateAdd() DateDiff() method above is a bit quicker.