Calculating datediff on runtime field

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calculating datediff on runtime field

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2009-03-19 : 05:10:22

Dear all,
I am using sql server 2005.
I am preparing a stored procedure where I calculate datediff based on dynamic column value. That is I first get appropite filed name based on some condition then calculate datediff on that field of the table. Problem is datediff does not recognize filed value

First I get field name as
select @field1 =ear_field from cms_event_att_rel where ear_eventid = @cnt and ear_first = 1

second I calculate datediff as
select datediff(dd, getdate() ,
dateadd(dd , @field1-1 , dateadd(month,@field2-1,dateadd(year,2009-1900,0))) ) from talbt

it show Conversion failed when converting the varchar value 'CA_DD_DOB' to data type int.

that is it does not recognizing @field1 as filed

please suggest

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-19 : 05:18:28

You will have to use DYNAMIC SQL for this.
See http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.63"
N 56°04'39.26"

matty
Posting Yak Master

161 Posts

Posted - 2009-03-19 : 05:21:19

What is the datatype of @field1 and @field2? I think they are character and you are using it in DATEADD function.

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2009-03-19 : 05:33:37

quote:
Originally posted by matty

What is the datatype of @field1 and @field2? I think they are character and you are using it in DATEADD function.

yes but i come to know filed name at runtime only i used dynamic sql but error remains the same

matty
Posting Yak Master

161 Posts

Posted - 2009-03-19 : 06:01:07

You cannot add a character value and an integer value.
For eg: Following returns error

DECLARE @field1 VARCHAR(10)
SET @field1 = 'a'
SELECT dateadd(dd ,1 + @field1,getdate())

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2009-03-19 : 06:13:58

quote:
Originally posted by matty

You cannot add a character value and an integer value.
For eg: Following returns error

DECLARE @field1 VARCHAR(10)
SET @field1 = 'a'
SELECT dateadd(dd ,1 + @field1,getdate())

No actually @field1 is name of field of table which is int datetype. When i tried with below stmt it works but when i pass fieldname as veriable then it showing me error
select dateadd(dd , ca_dd_dob-1 , dateadd(month,ca_mm_dob-1,dateadd(year,2009-1900,0))) from CMS_CUSTOMER_ATTRIBUTES

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-19 : 06:26:19

[code]DECLARE @SQL VARCHAR(1000)

SET @SQL = '
SELECT DATEADD(DAY, ' + QUOTENAME(@Field1) + ' - 1, DATEADD(MONTH, ' + QUOTENAME(@Field2) + ' - 1, DATEADD(YEAR, ' + CAST(@Year AS VARCHAR(11)) + ' - 1900, 0)))
FROM CMS_CUSTOMER_ATTRIBUTES'

EXEC (@SQL)[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources