May 24, 2007 at 9:22 am
I am writing business dateadd functions, so if you need 5 business days you would ignore weekends and holidays based on the appropriate holiday calendar. We look at bank holidays and exchange holidays in various locales, and in the case of some reporting, it's the union of UK and US bank holidays. If you wanted to look a year ahead you would use dateadd and then backup or advance the date (if it were a holiday) depending on the situation.
I was hoping to write a function with a parameter that I could pass directly to the built-in dateadd. The syntax would look like:
SELECT DateAddBusiness_fn(dd, @Increment, @Somedate, more params...) or
SELECT DateAddBusiness_fn(yy, @Increment, @Somedate, more params...)
The datepart dd cannot be passed as is in its naked state. But dateadd won't take a string. Hence my (now solved) problem.
February 16, 2010 at 3:26 pm
Why dont you try something like this if you dont want to use dynamic SQL
declare @unit nvarchar(5)
declare @value int
declare @today datetime
set @unit = 'YY'
set @value = 1
set @today=getdate()
select case @unit
when 'YY' then dateadd(year, @value, @today)
when 'MM' then dateadd(month, @value, @today)
when 'WW' then dateadd(week, @value, @today)
when 'DD' then dateadd(day, @value, @today)
end
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply