Variable for the datepart in DATEADD Function

Question

Variable for the datepart in DATEADD Function

cs.asbuilts

Old Hand

Points: 307
More actions
September 21, 2012 at 1:02 pm

#268025

I am trying to use the DATEADD function by passing in the value for DATEPART from a field to determine the next date. Is it possible to use a convert() or cast() to get a varchar into the expected parameter type for the date part?
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
todo_datedatetime,
interval_typeVARCHAR(10),
interval_freqint
)
--===== Insert the test data into the test table
INSERT INTO #mytable
(todo_date, interval_type, interval_freq)
SELECT getdate(), 'day', 7 UNION ALL
SELECT getdate(), 'month', 2 UNION ALL
SELECT getdate(), 'year', 1
select * from #mytable
select todo_date, DATEADD(interval_type, interval_freq, todo_date) as next_date from #mytable
If I specifically put in the datepart, such as:
select todo_date, DATEADD(day, interval_freq, todo_date) as next_date from #mytable
it works fine.
Just not sure if the DATEADD function is the way to go or if I have to write my own. The datepart values will always be day, month, or year.
Any suggestions are appreciated.
Thanks,
Craig

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 1

According to BOL, datepart Is the part of date to which an integer number is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

User-defined variables are allowed for the other two arguments, as you found out.

You would need to resort to dynamic SQL to do this, so far as I know.

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 2

You might just be able to use a CASE statement to determine which datepart to use in your DATEADD:

select todo_date, CASE interval_type

WHEN 'day' THEN DATEADD(day, interval_freq, todo_date)

WHEN 'month' THEN DATEADD(month, interval_freq, todo_date)

WHEN 'year' THEN DATEADD(year, interval_freq, todo_date) END

as next_date from #mytable

MWise

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 3

Excellent!

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

cs.asbuilts Old Hand Points: 307 More actions · Answer 4

That's perfect!

Thanks for snapping me back into reality. The simplest solutions tend to be the best.

Thanks,

Craig