DATEDIFF Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATEDIFF Function

Author

Topic

kurtgr
Starting Member

25 Posts

Posted - 2009-09-29 : 13:00:38

Hi guys I am having a problem I am pass variable to the datediff function but i am getting this error

Msg 295, Level 16, State 3, Line 11
Conversion failed when converting character string to smalldatetime data type.

Below is the code i am using

declare @LifeSpan TINYINT, @Period CHAR(6), @Amount REAL,
@Cost MONEY, @AssetPackageId INT,
@sql varchar(150),@TimeOfIssue SMALLDATETIME

SET @LifeSpan = 3
SET @TimeOfIssue = getdate()
SET @Period = 'MONTH'
SET @AssetPackageId = 2

SET @Sql = 'SELECT @DateDiff = DATEDIFF('+@Period+','+@TimeOfIssue+',GETDATE())'

select @sql
exec(@sql)

Can someone assist me in saying or showing what am i do wrong

Thanks in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-29 : 13:12:48

You are building a sql string, but you are trying to concatenate a string(@period) with a datetime(@timeOfIssue). Try this

SET @Sql =
'
DECLARE @DateDiff int

SELECT @DateDiff = DATEDIFF('+@Period+','+''''+convert(varchar(50),@TimeOfIssue)+''''+',GETDATE())
'

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:14:31

you cant return values like this in dynamic sql using EXEC. you need to use sp_executesql.
whats the purpose of dynamic sql here?.

kurtgr
Starting Member

25 Posts

Posted - 2009-09-29 : 13:18:36

Thanks very much it worked

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 02:13:31

In fact, you van avoid dynamic sql in this case

declare @LifeSpan TINYINT, @Period CHAR(6), @Amount REAL,
@Cost MONEY, @AssetPackageId INT,
@sql varchar(150),@TimeOfIssue SMALLDATETIME

SET @LifeSpan = 3
SET @TimeOfIssue = getdate()
SET @Period = 'MONTH'
SET @AssetPackageId = 2

IF @Period = 'MONTH'

SELECT @DateDiff = DATEDIFF(MONTH,@TimeOfIssue,GETDATE())

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources