I have an expression in a view that calculates a date by adding a
number of months using the dateadd function.

I want to get both the datepart (m for month in my caase) and hte
number (1-3 months) from other fields in my view.

I've tried this
DATEADD(dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_Dat eAddInterval, -
dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_BaseUnit,
dbo.VW_110_01_ChargePeriod.EWC_PeriodEndDate)

but it bomb out with I try and run it with the following error:

"dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_DateAddInt erval is not a valid
dateadd option"

The field dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_DateAddInte rval holds
a text value of 'm'

If I replace the datepart with m, it works, why cant I pass my value
from dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_DateAddInte rval ?

Thanks in advance

Grant

NZDeveloper (grant (AT) technologyworks (DOT) co.nz) writes:
Because you can't. And if you think of it's logical. Say that you have:

CREATE TABLE crap (m varchar(23) NOT NULL)
go
INSERT crap (m) VALUES('DAY')

SELECT dateadd(m, 1, getdate())
FROM crap

If it would be possible to pass a column here, how would SQL Server
disambguiate between the column and the possible constant?



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland Sommarskog (esquel (AT) sommarskog (DOT) se) writes:

Forgot to say: you need to use a CASE expression:

CASE yourcol
WHEN 'm' THEN dateadd(MONTH, period, date)
WHEN 'd' THEN dateadd(DAY, period, date)
...
END

Alternatively, write a CLR function that accepts the datepart as a
parameter.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Jul 1, 7:05*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks ...

The Case suggestion works

CASE dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_DateAddInte rval WHEN 'm'
THEN DATEADD(m , - dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_BaseUnit ,
dbo.VW_510_01_ChargePeriod.EWC_PeriodEndDate) WHEN 'ww' THEN
DATEADD(ww , - dbo.tbl_PTDF_PreTreatDevFrequency.PTDF_BaseUnit ,
dbo.VW_510_01_ChargePeriod.EWC_PeriodEndDate) END