title: How to Add Year & Month to my temporary Transaction Number using stored procedure
body: <p>I want to have a result like this (USR2019-12-000001) below is my code.
  when i add to declare &amp; set year/month i got error 
  "Conversion failed when converting date and/or time from character string."</p>
  
  <pre><code>//Original code that got error
  ALTER PROCEDURE [dbo].[spAutoCustom_number]
  AS
  BEGIN
      declare @max int
      set @max =0;
      declare @trn_name varchar(5)
      set @trn_name='USR';
  
      declare @trn_year datetime
      set @trn_year=DATEDIFF(YYYY, 0, GETDATE());
      declare @trn_month datetime
      set @trn_month=DATEDIFF(MM, 0, GETDATE());
  
      declare @trn_No varchar(20)
      declare @start int
      set @start=1;
      declare @no varchar(10)
      create table #user(trn_No varchar(20))
      select @max=MAX(Cast(right(UserNumber,6) as int)) + 1 from UsersList
  
      if (@start&lt;=@max)
      begin
      set @start=@max;
      end
      set @no=Right('000000'+ CAST(@start AS varchar(10)),6)
      select @trn_No=@trn_name+@trn_year+('-')+@trn_month+('-')+@no
      insert into #user values(@trn_No)
      select * from #user
  
  END
  </code></pre>