CODEWITHSUNDEEP
sqlsql-serverdate
Pomster
Pomster

Reputation: 15197

Convert Date into nvarchar?

I am trying to create a unique value feild which will hold the current ID, and the current date. @QuoteNumber is the unique field i want to create.

So far i think im on the right track but i'm getting an error when trying to add the date into my nvarchar.

DECLARE @QuoteNumber nvarchar(50) 
DECLARE @Date varchar(10)
SELECT @Date = (LEFT(CONVERT(VARCHAR, GETDATE(), 120), 10))
SET @QuoteNumber = 'QU-' + @Date + '-' + SCOPE_IDENTITY()

Error:

Error converting data type varchar to numeric.

Edit

Iv also tried:

SET @Date = CONVERT(varchar(10), GETDATE(), 20)

Upvotes: 0

Views: 589

Answers (3)

M.Ali
M.Ali

Reputation: 69524

DECLARE @QuoteNumber nvarchar(50) 
DECLARE @Date varchar(10)
SELECT @Date =  CONVERT(VARCHAR(10),GETDATE(),121)
SET @QuoteNumber = 'QU-' + @Date + '-' + CAST(SCOPE_IDENTITY() AS VARCHAR(10))

Its not date which is causing issue it is SCOPE_IDENTITY() function which returns INT data and you are trying to concatenate Int value with a string. Because INT is a data type with higher presidence sql server tries to convert the string to INT and fails and you get the error you get.

Upvotes: 2

Jeremy Cook
Jeremy Cook

Reputation: 22063

SCOPE_IDENTITY() is causing that error. You need to cast it to an varchar as well.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Try doing it like this:

SELECT @Date = CONVERT(VARCHAR(10), GETDATE(), 120);

You should always use a length with varchar(), although that doesn't affect this query.

Then, scope_identity() returns a number, so you need to conver that:

SET @QuoteNumber = 'QU-' + @Date + '-' + cast(SCOPE_IDENTITY() as varchar(255);

Upvotes: 2

Related Questions