CODEWITHSUNDEEP
sql-server
pepe
pepe

Reputation: 149

Custom autogenerated ID in sql server

How can I create a custom auto-generated ID in this format:

yyyymmdd-xxxxx

where:

and the next day xxxxx should be restarted to 0.

Thanks

Upvotes: 0

Views: 573

Answers (2)

John
John

Reputation: 53

You might consider including an Identity column and a datetime column that will be set on insert and than add a calculated column to put the two together.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

  • Create one autonumeric ID column in your db
  • save the date/time of each insert row.

Then use a ROW_NUMBER() function

SELECT *,
       dayField + '-' + CAST(rn AS VARCHAR(100))
FROM (
        SELECT  ID, dateTimeField,
                -- truncate the time and convert to yyyymmdd
                CONVERT(VARCHAR(10), cast(dateField As Date), 112) as dayField,        
                ROW_NUMBER() OVER (PARITION BY cast(dateField As Date)
                                   ORDER BY dateTimeField) as rn
                                  -- or just ORDER BY ID
     ) T

Now if you want save this on the db, you probably will need a trigger.

Upvotes: 1

Related Questions