SQL Server Performance Forum – Threads Archive
How to convert time in integer
Dear all, Can I have anyone to guide me on the issue about time format? Field: CreatedTime
DataType: int
Data: 65741 The on screen result will show 06:15:41 pm as the output.
Can anyone teach me how? Thanks.
Kareene
this will convert the int time (seconds since midnight) to datetime.
select dateadd(second, 65741, 0) you can use convert() to format it or do the formating in your front end application
KH
>>you can use convert() to format it or do the formating in your front end application<br /><br />I always prefer formating at front end if it used [<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Dear KhTan, thanks for the statement "select dateadd(second, 65741, 0)" If another way, i would like to know how the time insert into the integer field. Use back the example. Current time is "06:15:41 pm", what integer should appear in the field? Fyi, there are no front end applicaion created, i just want a sql update statement for this.
Thanks.
Kareene
Actually there is already an integer stored in a DATETIME column. It’s a BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> and the second 4 bytes contain the clock ticks since midnight. So, just for fun, here’s a small and simpole calculation:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @dt DATETIME<br />DECLARE @TageSeit19000101 INTEGER<br />DECLARE @ClockTicks INTEGER<br /><br />SET @dt = GETDATE()<br />SET @TageSeit19000101 = CAST( SUBSTRING(CAST(@dt AS BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) AS INT)<br />SET @ClockTicks = CAST(SUBSTRING(CAST(@dt AS BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),5,4) AS INT)<br /><br />SELECT <br /> @dt AS JetztGerade<br /> , @TageSeit19000101 AS AnzTage<br /> , CAST(@TageSeit19000101 AS DATETIME) HeuteOhneZeit<br /> , @ClockTicks AS CT<br /> , @ClockTicks / (300 * 60 * 60) AS Stunden<br /> , (@ClockTicks % (300 * 60 * 60)) / (300 * 60) AS Minuten<br /> , ((@ClockTicks % (300 * 60 * 60)) % (300 * 60)) / (300) AS Sekunden<br /> , CAST(ROUND(((((@ClockTicks % (300 * 60 * 60)) % (300 * 60)) % (300)) / 3.0) * 10,0) AS INT) AS MS<br /><br />JetztGerade AnzTage HeuteOhneZeit CT Stunden Minuten Sekunden MS <br />—————————————————— ———– —————————————————— ———– ———– ———– ———– ———– <br />2006-11-14 17:09:44.990 39033 2006-11-14 00:00:00.000 18535497 17 9 44 990<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
"Current time is "06:15:41 pm", what integer should appear in the field?"
65741 is the number of seconds since midnight 00:00:00 Why are you using integer to store the time ? Why don’t you use datetime instead ?
KH
Dear all, Thanks for helping. I have the solution by create a small calculation.
Thank you.
Regards,
Kareene
]]>