Post reply

DATETIME AS PrimaryKey?

  • December 1, 2005 at 8:40 am

    #166701

    DATETIME AS PrimaryKey?

    declare @dtpk smalldatetime

    select  @dtpk=getdate()

    print @dtpk

    in the above, i m using @dtpk as a primary key value in a table to keep a track of some events that are occuring with a difference of one second. the above method gives me value to store in the Primary key Filed (EventTime smalldatetime) in the form "Dec  1 2005  7:57PM" where my requirement is to save it in the form "Dec  1 2005  7:57:12.03PM" so as to make it unique for the primary key field(EventTime smalldatetime). how to get required datetime format with the help of above statements to store as primary key  in EventTime field of type smalldatetime.

    THNX IN ADAVNCE

  • December 1, 2005 at 9:29 am

    #607100

    I am pretty sure you are going to have to switch to use DATETIME instead of SMALLDATETIME.  SMALLDATETIME stores Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute.  Values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute

  • December 2, 2005 at 10:15 am

    #607437

    DATETIME is a better choice than SMALLDATETIME, but I would hesitate to use either as a primary key.  Consider the effect of needing to adjust your timeclock backwards, or having data introduced from muliple sources, each with their own timeclocks.

    Regards

     

  • December 2, 2005 at 10:22 am

    #607439

    Here's a couple of other options:

    • an IDENTITY column for the PK and just index datetime
    • any other unique column for the PK and just index the time

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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