按日期生成编号

 1

create view v_GetDate
as
SELECT DT=CONVERT(CHAR(6),GETDATE(),12)
GO

CREATE TABLE TTB(
BH CHAR(12) ,
COL INT)

GO

  DECLARE @DT CHAR(6),@NEWBH CHAR(12)
  SELECT @DT=DT FROM V_GETDATE
  SET @NEWBH=(
 SELECT @DT+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
 FROM TTB WITH(XLOCK,PAGLOCK)
 WHERE BH LIKE @DT+'%')
  PRINT @NEWBH

 2

declare @NewMainID char(15)

SELECT top 1 @NewMainID=mainid FROM You_MainTable
WHERE companyid=@IN_COMPANYID AND LEFT(mainid,8)=CONVERT(VARCHAR(8),GETDATE(),112)
order by  mainid desc
  
if @NewMainID is null
begin
 SET @NewMainID=CONVERT(VARCHAR(8),GETDATE(),112)+'0000001'
-- SET @NewMainID=REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),'-','')+'0000001' 
end
else begin
 SET @NewMainID=LEFT(@NewMainID,8)+RIGHT(CONVERT(INT,RIGHT(@NewMainID,7))+10000001,7)
end