Using a case statment in the Dateadd function

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 2000 Forums

SQL Server Development (2000)

Using a case statment in the Dateadd function

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-02 : 20:16:55

I would like to use a case statment for the dateadd function within a query, but it will not allow me.

TBL_CYCLE contains two columns, CYCLE(Contains the CycleID),Increment(Contains the incrementing number)

select *, DateAdd(case a.Cycle
when 1 then 'Month'
when 2 then 'Week'
When 3 then 'Week' --BiMonthly
When 4 then 'Quarter'
when 5 then 'Quarter' --SemiAnnually
when 6 then 'Year' end, case a.cycle
When 1 then a.Increment
when 2 then a.Increment
When 3 then a.Increment * 2
When 4 then a.Increment
when 5 then a.Increment * 2
when 6 then a.Increment end,'01/01/2005') as AdjustedDate
From tbl_CYCLE a

It will not allow me to use a case statment within a function. Is there any way around this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-02 : 20:30:13

Do it with a User Defined Function.

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-02 : 20:42:57

Are you saying to create 2 seperate user defined functions one for the timeframe, and one for the increment calculations? I see how this will work, but do you feel this is the best approach?

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-02 : 20:44:54

See if this works for you:

SELECT *,
CASE a.Cycle
WHEN 1 THEN DATEADD(month, a.Increment, '01/01/2005')
WHEN 2 THEN DATEADD(week, a.Increment, '01/01/2005')
WHEN 3 THEN DATEADD(week, a.Increment * 2, '01/01/2005')
WHEN 4 THEN DATEADD(quarter, a.Increment, '01/01/2005')
WHEN 5 THEN DATEADD(quarter, a.Increment * 2, '01/01/2005')
WHEN 6 THEN DATEADD(year, a.Increment, '01/01/2005')
END AS AdjustedDate
FROM tbl_CYCLE a

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-02 : 20:51:55

Perfect Nosepicker! That's what I was looking for. Isn't it strange how after you see the solution it's so blatently obvious..

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-02 : 21:20:04

quote:
Originally posted by Vinnie881

Are you saying to create 2 seperate user defined functions one for the timeframe, and one for the increment calculations? I see how this will work, but do you feel this is the best approach?

I said "Do it with a User Defined Function.", meaning a single one.

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-02 : 23:50:05

I appriciate your help Michael, but your original response was not obvious to me regarding how to resolve my issue using "a" single function. Using the methodology I illustrated in my post I did not understand how 1 function would work, however I could make it work using not "a", but "2" functions; hense my response. It was not until nosepickers post, that I learned the correct methodology to use, and now I see how a single function makes sense. My forte is C++,VB.net,C#, and Java, so I know the programmers mentallity of I'm smart, and if you don't get what I say your dumb, but I have been working with t-sql for 3 months, and if I knew everything about it then I would not need to post to this forum. I do appriciate your help, but there really isn't a need for the "what, can't you read?" response underlining your text like I'm a 3 year old.

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-03 : 02:28:20

[code]CREATE FUNCTION dbo.fnAdjustedDate
(
@StartDate DATETIME,
@Cycle TINYINT,
@Increment INT
)
RETURNS DATETIME
AS

BEGIN
RETURN (
SELECT CASE
WHEN @Cycle = 1 THEN DATEADD(month, @Increment, @StartDate)
WHEN @Cycle = 2 THEN DATEADD(week, @Increment, @StartDate)
WHEN @Cycle = 3 THEN DATEADD(week, 2 * @Increment, @StartDate)
WHEN @Cycle = 4 THEN DATEADD(quarter, @Increment, @StartDate)
WHEN @Cycle = 5 THEN DATEADD(quarter, 2 * @Increment, @StartDate)
WHEN @Cycle = 6 THEN DATEADD(year, @Increment, @StartDate)
END
)
END[/code]Call with[code]SELECT a.*,
dbo.fnAdjustedDate('1/1/2005', a.Cycle, a.Increment)
FROM tbl_CYCLE a[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources