locked
SQL Server time format RRS feed

  • Question

  • Hi, I tried to take advantage of the new time format offered in SQL Server 2008, but I come to realize that the upper bounds for that is 23:59:59.9999999..

    What is the best way to store a time value above this? Do we have to use a float value that represents the time we want?

    thanks,

    sivilian

     

    Tuesday, November 15, 2011 9:57 PM

Answers

  • HI !

    Usually it is stored like, how many hours a person has wrked on specific project. It is not like how many minutes or seconds he has spent on it.

    So here you can go with NumberOfHoursSpentOnProject with Integer DataType.

    Thanks, Hasham

    • Marked as answer by sivilian Wednesday, November 16, 2011 2:41 PM
    Wednesday, November 16, 2011 2:24 PM
    Answerer

All replies

  • HI sivilian !

    Since MS SQL Server 2008 provides Time Data Type and it can provide precision upto (100ns) so there is no point storing them into some other data type.

    If you are thinking of '24:00:00:0000000' to be considered as same day , no this will be considered as next day time.

    SELECT CAST(CURRENT_TIMESTAMP AS TIME)
    --Time
    --07:41:31.3330000
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

    Wednesday, November 16, 2011 2:46 AM
    Answerer
  • A time(7) datatype is appropriate for values 00:00:00.0000000 through 23:59:59.9999999.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi NEditor Wednesday, November 16, 2011 4:42 AM
    Wednesday, November 16, 2011 3:34 AM
    Answerer
  • Thanks for the answers. I think I got part of it... That the time datatype should not be used for what I need.

    Basically, I need to store a time value, for example: the length of time someone worked on a specific project. Since this value can exceed 24 hours, as I undertsand, using the time datatype is inappropriate. Now, my question is what is the best datatype to use. Should I use, say integer, and consider it seconds, and cast it out in the application that reads it?

    thanks,

    sivilian

     


    • Edited by sivilian Wednesday, November 16, 2011 1:53 PM
    • Marked as answer by sivilian Wednesday, November 16, 2011 2:41 PM
    • Unmarked as answer by sivilian Wednesday, November 16, 2011 2:41 PM
    Wednesday, November 16, 2011 1:53 PM
  • HI !

    Usually it is stored like, how many hours a person has wrked on specific project. It is not like how many minutes or seconds he has spent on it.

    So here you can go with NumberOfHoursSpentOnProject with Integer DataType.

    Thanks, Hasham

    • Marked as answer by sivilian Wednesday, November 16, 2011 2:41 PM
    Wednesday, November 16, 2011 2:24 PM
    Answerer
  • Thanks... I need the precision to go down to the seconds though. But I'll take that as affirmation.

    sivilian

    Wednesday, November 16, 2011 2:40 PM