Hi! I am trying to create a numbering field in a table but i want to be reset every year. I would like to have this format yyyy/00000 e.g 2010/00001 2010/00002 2010/00003 2011/00001 2011/00002 2012/00001 2012/00002 2012/00003
I am using this trigger REATE TRIGGER insertReceiptNoLong ON dbo.RECEIPTS after INSERT AS Declare @longID varchar(50), @idLength int, @seedValue varchar(5)
--Check if the seed is 5 digits long set @idLength = 5 - (select len(convert(varchar,@@IDENTITY))) set @seedValue = convert(varchar,@@IDENTITY) --if the seed length is less than file padd extra zeroes on left side while @idLength > 0 Begin set @seedValue = '0' + @seedValue set @idLength = @idLength - 1 End
--join everything together set @longID = convert(varchar,year(getdate())) + '/'+ @seedValue update RECEIPTS set ReceiptNoLong = @longID where RECEIPTNo = @@IDENTITY
The RECEIPTNo is an autonumbering filed in the table RECEIPTS.
Custom numbering field based on year
Posted - 06/14/2012 : 02:53:07
