SQL Server Performance

How to convert time in integer

Discussion in 'SQL Server 2005 General DBA Questions' started by travistan, Nov 13, 2006.

  1. travistan New Member


    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




  2. khtan New Member

    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
  3. Madhivanan Moderator

    &gt;&gt;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
  4. travistan New Member

    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
  5. FrankKalis Moderator

    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>
  6. khtan New Member

    "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
  7. travistan New Member

    Dear all,

    Thanks for helping. I have the solution by create a small calculation.


    Thank you.


    Regards,
    Kareene

Share This Page