none
Time Formats RRS feed

  • Question

  •  

    OK - totally new to this game but here goes ...

    I want to put together a database to record times achieved by swimmers in competition.  The database itself is simple but I don't know what format to use to store the time achieved.  Times can vary, for example 35.27 (SS.00), 1.12.63 (MM.SS.00). Oh, and by the way I want to rank the output but haven't even begun to think about that yet!

    Wednesday, March 7, 2007 8:52 PM

Answers

  • You would need to enter the time in the format: hh:mm:ss.mm '00:00:00.x'.

    To test this, in a query window,


    DECLARE @MyTime datetime
    SET @MyTime = '00:00:03.27'

    SELECT @MyTime
    SELECT convert( varchar(12), @MyTime, 114 )

    Wednesday, March 7, 2007 10:14 PM
    Moderator

All replies

  • SQL Server does not have a field datatype for JUST time values.

    However, if you use a datetime datatype, and store time values, you will be able to easily do time math. The key is to remember that in SQL Server, datetime data is stored in a decimal notation -the left of decimal portion translates to the date, and the right of decimal portion translates into hours, minutes, seconds, and milliseconds.

    So if you store only a time, the date portion is zero, and when you display the values, it will date that translates to zero -which is Jan 01, 1900. Since you are only using the time portion, you ignore the date portion. Look in Books Online for the keywork [ convert ], or [ cast and convert ] for more details.

    Since you will want to do date/time math, the datetime datatype is your best option.

    Wednesday, March 7, 2007 9:32 PM
    Moderator
  • Any thoughts on how to enter the data as when I have datetime set I can't just the time.  In my examples how would I enter say 35.27 and 1:12.63?
    Wednesday, March 7, 2007 9:52 PM
  • You would need to enter the time in the format: hh:mm:ss.mm '00:00:00.x'.

    To test this, in a query window,


    DECLARE @MyTime datetime
    SET @MyTime = '00:00:03.27'

    SELECT @MyTime
    SELECT convert( varchar(12), @MyTime, 114 )

    Wednesday, March 7, 2007 10:14 PM
    Moderator
  • Looks good.  Thank you.
    Wednesday, March 7, 2007 10:52 PM