2

Is their any simple way to auto generate a project id like 0001, 0002, 0003 but instead of 0001 its 2014-0000001, 2014-0000002, 2014-0000003 ...

 UserID = {YEAR}-{0000001} > continuous.

Sample:

          UserID | Name
    2014-0000001 | Gary
    2014-0000002 | Alrey

or generating a csharp/c# function is much easy then manully inserted to table then get the latest UserID and increment but base on year? what can you suggest.

i have here sql command working but when the year pass by it wont reset to 1. i have an idea when counting rows where year is current date.

CREATE TABLE Test(
        TestID INT IDENTITY(1,1) NOT NULL,
        TestPrefixID AS (CONVERT([VARCHAR], YEAR(CURRENT_TIMESTAMP), 0)) + '-'  + RIGHT('000000'+CONVERT([VARCHAR],TestID,0), (6)),
        Name VARCHAR(100) NOT NULL,
        CONSTRAINT PK_Teacher PRIMARY KEY (TestID)
)

thanks in advance.

Pseudorandom
  • 716
  • 3
  • 14
  • 30
  • 3
    You can use an `IDENTITY` column for generating IDs `1, 2, 3, ...` and then use computed column to generate a new column in a format that you want – dotnetom Oct 15 '14 at 04:49
  • 1
    Cheeck this out.. http://stackoverflow.com/questions/16800383/how-do-i-add-a-autoincrement-primary-key-in-sql-server-with-nvarchar – NMK Oct 15 '14 at 04:49
  • So when year change will it be like 2015-0000001 .... ??? start with one again ? – H. Mahida Oct 15 '14 at 04:54
  • @H.Mahida yes. exactly. – Pseudorandom Oct 15 '14 at 04:55
  • Why even *store* this? You can always calculate this from the `ID` (`INT IDENTITY`) of the user account, and the `YEAR(SignupDate)` .... – marc_s Oct 15 '14 at 05:13
  • because it's the user identity key. but i think i almost got it check this out http://paste.ubuntu.com/8562973/ but the problem is when year pass by. TestID column will always increment. hmmm @H.Mahida – Pseudorandom Oct 15 '14 at 05:24

0 Answers0