Wednesday, April 11, 2012

TSQL - Date and Formated Count

On the project I'm working on I need to set a field to the to a two digit date and sequential count number when a new record is added. The format is YY-### where YY is the year and ### is the count. So the first record for 2012 would be 12-001, the next is 12-002, and so on.

This is how I did it.

DECLARE @ret int
DECLARE @count int
DECLARE @val varchar(6)
--get the two digit year
SET @ret = (YEAR(GETDATE()) % 100)
--count the number of records where the controlnum begins with the year and add 1
select @count = count(*)+1 from parosc where controlnum like CONVERT(varchar, @ret) + '%'
set @val = CONVERT(varchar(2), @ret) + '-' + right('00' + CONVERT(varchar, @count), 3)

I just made it into a User Defined Function and set the default value for the ControlNum field to the function.

No comments:

Post a Comment

Thank you for commenting!