Converting varchar to bigint in T-SQL

Question

I am doing calculation part in SQL for my web app,

I have some scalar variables:

declare @calc as bigint
declare @paymentid as nvarchar(50)

and I am using the variables like this

set @calc =(SELECT REPLACE(CONVERT(CHAR(30), '2017-09-02', 103), '-', ''))     

This line removes '-' and sets the value in @calc to (20170902)

set @calc = (SELECT SUBSTRING(CONVERT(char(30), '2017-09-02',112), 3, 8))    

This line is fetching 170902 and sets the value in @calc to (170902)

set @paymentid =  CAST(@calc as nvarchar(max))+'0001'

and this line will set the value of paymentid to (1709020001)

But I am getting an error on this line

set @calc = (SELECT SUBSTRING(CONVERT(char(30), '2017-09-02',112), 3, 8))    

and the error is:

Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to bigint

My whole code looks like this

declare @calc as bigint
declare @paymentid as nvarchar(50)

set @calc =(SELECT REPLACE(CONVERT(CHAR(30), '2017-09-02', 103), '-', ''))     
set @calc = (SELECT SUBSTRING(CONVERT(char(30), '2017-09-02',112), 3, 8))    
set @paymentid =  CAST(@calc as nvarchar(max))+'0001' 

Answer 1

Replace

set @calc = (SELECT SUBSTRING(CONVERT(char(30), '2017-09-02',112), 3, 8))   

to

set @calc = (SELECT SUBSTRING(CONVERT(char(30), @calc,112), 3, 8))   

Answer 2

If you've got SQL Server 2012 or later, you can do this:

declare 
    @now date = getdate(), -- using getdate, but you can put in a hard coded or provided date as necessary
    @paymentid nvarchar(50)

select @paymentid = format(@now, 'yyMMdd0001')

select @PaymentId --1709010001

or more succinctly....

select @paymentid = format(getdate(), 'yyMMdd0001')

Answer 3

You can make this a lot shorter...

declare @paymentid as nvarchar(50)
declare @someDate date = '2017-09-02'

set @paymentid = convert(varchar(12),@someDate,12) + '0001'
select @paymentid