CODEWITHSUNDEEP
sql-servertype-conversionbigint
user7067778
user7067778

Reputation:

Converting varchar to bigint in T-SQL

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'

Upvotes: 0

Views: 3663

Answers (3)

Xedni
Xedni

Reputation: 4715

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')

Upvotes: 0

S3S
S3S

Reputation: 25152

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

Upvotes: 1

user6656728
user6656728

Reputation:

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))

Upvotes: 0

Related Questions