Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Question Unanswered: Custom Date Key???

    I am a complete SQL NooB , I am looking to create a custom GETDATE() and use it as a key...

    Format: AL= ACTIVITY LOG

    AL-MMDDYYYY-1

    AL-MMDDYYYY-2

    AL-MMDDYYYY-3

    and so on...

    I need the number 1,2,3... to recycle every 24hrs,

    so every new date/day (midnight server time) the number attached to the date would cycle back to 1, then 2, then 3 and so on...

    Does that make sense?



    Another Example:

    AL-01012011-1 = January 1st 2011 entry 1

    AL-01012011-2 = January 1st 2011 entry 2

    AL-01012011-3 = January 1st 2011 entry 3
    -----------------------------------------------
    AL-01022011-1 = January 2nd 2011 entry 1

    AL-01022011-2 = January 2nd 2011 entry 2




    Thank you!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Some thoughts on making a primary key with this format: AL-MMDDYYYY-1.

    1. When "AL-" occurs in every column, drop it. It is redundant.
    2. "MMDDYYYY" that is a standard NOT NULL DATE
    3. When "-" occurs in every column, drop it. It is redundant.
    4. "1" restarting every 24 hours. Why is that? If it would start from value 256 at 00:00:00 the next day, and 1346 a day later, what would be the problem?

    When 4 is no problem, the number may continue, you can simply revert to using an identity column:
    Code:
    CREATE TABLE DaTable(
    	Id		BIGINT	IDENTITY(1, 1)	NOT NULL,
    	DaDate	DATE	NOT NULL	DEFAULT GetDate(),
    	PkDisplay AS ('AL-' + REPLACE(CONVERT(VARCHAR(10), DaDate, 101), '/', '') + '-' + CAST(Id as VARCHAR(20))) PERSISTED,
    	CONSTRAINT pk_DaTable PRIMARY KEY (Id)	
    	)
    PkDisplay is a calculated column. The value is calculated once and stored in the table (by specifying "PERSISTED"). When you do not use PERSISTED, your table will take less space, but you won't be able to create an index on it.

    The real primary key is Id, but you never show it, you use PkDisplay instead.
    Last edited by Wim; 03-14-11 at 07:37.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •