declare @count int
set @count = select count(1)+1 from table where ....
select cast(year(getdate())as char(4))+
       case when month(getdate())<10 then '0'+cast(month(getdate()) as char(1)) else cast(month(getdate()) as char(2)) end+
       case when day(getdate())<10 then '0'+cast(day(getdate()) as char(1)) else cast(day(getdate()) as char(2)) end+
       isnull(replicate('0',4-len(@count)),'')+cast(@count as char(4))