CODEWITHSUNDEEP
sqlsql-server-2008
PuroRock
PuroRock

Reputation: 83

SQL Conversion Error

I really need a second pair of eyes on this... I have a Stored Procedure with a variable:

DECLARE @date_period_end int 

IF @date_period_end = '' or @date_period_end is null  
BEGIN 
    SET @date_period_end = 'DATEDIFF(dd, ''1/1/1753'', CONVERT(datetime,getdate())) + 639906 ' 
END

Here is the error SQL returning when I execute:

Conversion failed when converting the varchar value 'DATEDIFF(dd, '1/1/1753', CONVERT(datetime,getdate())) + 639906 ' to data type int.

I'm pretty certain I'm missing or have added an incorrect amount of quotes somewhere. Thanks.

Upvotes: 0

Views: 252

Answers (5)

Sachin Tanwar - .Net Developer
Sachin Tanwar - .Net Developer

Reputation: 347

DECLARE @date_period_end int

IF @date_period_end = '' or @date_period_end is null  
BEGIN 
SET @date_period_end = DATEDIFF(dd,'1/1/1753',CONVERT(datetime,getdate())) + 639906
END

you can also see the value of this variable using below statement along with the above code:

Print @date_period_end

Upvotes: 1

Joel Gallagher
Joel Gallagher

Reputation: 472

removing the extra quotes did the trick - and I've simplified your IF check using ISNULL

DECLARE @date_period_end int 

IF ISNULL(@date_period_end,'') = '' 
BEGIN 
    SET @date_period_end = DATEDIFF(dd, '1/1/1753', CONVERT(datetime,getdate())) + 639906 
END

Upvotes: 0

curtisk
curtisk

Reputation: 20175

Try this:

BEGIN
   SET @date_period_end = DATEDIFF(dd, '1/1/1753', CONVERT(datetime,getdate())) + 639906  
END

There were incorrect quotes around DATEDIFF() and double quotes around the dummy date

Upvotes: 1

bluetoft
bluetoft

Reputation: 5443

Looks like you have a quote around DATEDIFF(dd, '1/1/1753', CONVERT(datetime,getdate())) + 639906

remove the quotes and it should work.

Upvotes: 2

lc.
lc.

Reputation: 116438

You've got quotes around your DATEDIFF function, making it a string literal, and I'm pretty sure that's not what you intended. Try this:

DECLARE @date_period_end int 

IF @date_period_end = '' or @date_period_end is null  
BEGIN 
    SET @date_period_end = DATEDIFF(dd, '1/1/1753', CONVERT(datetime,getdate())) + 639906
END

Side note: I'm not sure why you're comparing an integer with the empty string (@date_period_end = '') and what you expect it will do.

Upvotes: 2

Related Questions