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.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-05-27 : 16:01:51
|
"Period" as in "a period of time".I need to implement a configurable cycle for a course. Could be weeks, months, year(s). I don't want to second guess that someday it won't need to be 45 days or something like that.The DATEADD requires "datepart" and an integer. My "Plan A" will store the period as both of these values (datepart and number) and call it a day. (no pun intended)  Is there a way to store a single value for the period, keep the flexability, and have it work well with DATEADD?Sam |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-05-27 : 16:29:18
|
| Here's a good one:DATEADD doesn't accept a variable as the first parameter. That'll make "Plan A" above difficult.Sam |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-05-27 : 16:33:08
|
| Maybe this topic isn't challenging enough for you guys and gals... Let me kick it up a notch ... Ka-Pow !!Given a start date, and period, give me a formula that identifies which iteration GETDATE() falls into. e.g. is Today the 1st, 2nd, or 3rd period?Sam |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-05-27 : 16:37:34
|
Well, I've implimented your "Plan A" and store "dd" and 1 in two separate colums to give me "Execute this Once per Day"Here's how I got around the DATEADD problem:CREATE FUNCTION fx_DateAdd(@Datepart VARCHAR(4), @Number INT, @Date DATETIME) RETURNS DATETIME AS BEGIN DECLARE @Result DATETIME SELECT @Result = CASE --Day WHEN @datepart = 'dd' OR @DatePart = 'd' THEN DATEADD(dd, @Number, @Date) --Month WHEN @datepart = 'mm' OR @DatePart = 'm' THEN DATEADD(mm, @Number, @Date) --Week WHEN @datepart = 'wk' OR @DatePart = 'ww' THEN DATEADD(wk, @Number, @Date) --Year WHEN @datepart = 'yy' OR @DatePart = 'yyyy' THEN DATEADD(yy, @Number, @Date) --Quarter WHEN @datepart = 'qq' OR @DatePart = 'q' THEN DATEADD(qq, @Number, @Date) --Day of Year WHEN @datepart = 'dy' OR @DatePart = 'y' THEN DATEADD(dy, @Number, @Date) --Hour WHEN @datepart = 'hh' THEN DATEADD(hh, @Number, @Date) --Minute WHEN @datepart = 'mi' OR @DatePart = 'n' THEN DATEADD(mi, @Number, @Date) --Second WHEN @datepart = 'ss' OR @DatePart = 's' THEN DATEADD(s, @Number, @Date) --Millisecond WHEN @datepart = 'ms' THEN DATEADD(ms, @Number, @Date) END RETURN @ResultEND <Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-05-27 : 18:32:58
|
I appreciate the solution and I guess it's Plan A for sure.No takers on the Period problem?I guess that it'll involve casting the difference in EndDate-StartDate into the quantities of the period (dd, mm, yy). Divide the result by the Number in the Period, add 1, that's the period for "today".I got stuck figuring how to cast the difference of the two dates into period quantities. My out was to convert StartDate into period quantities and go from there.Last, for no good reason, I looked at BOL. There it is:DATEDIFF(mm, StartDate, FinishDate) -- in the units of the specified period / @number -- The number of units in this period + 1 -- Because this doesn't start at zero This needs Michael's CASE statement to work. Sounds easier to write a user defined function.DROP FUNCTION dbo.UDF_DATEDIFFGOCREATE FUNCTION dbo.UDF_DATEDIFF(@EndDatePart VARCHAR(4), @StartDate DATETIME, @EndDate DATETIME)RETURNS INTASBEGINRETURN (CASE --Day WHEN @EndDatepart = 'dd' OR @EndDatePart = 'd' THEN DATEDIFF(dd, @StartDate, @EndDate) --Month WHEN @EndDatepart = 'mm' OR @EndDatePart = 'm' THEN DATEDIFF(mm, @StartDate, @EndDate) --Week WHEN @EndDatepart = 'wk' OR @EndDatePart = 'ww' THEN DATEDIFF(wk, @StartDate, @EndDate) --Year WHEN @EndDatepart = 'yy' OR @EndDatePart = 'yyyy' THEN DATEDIFF(yy, @StartDate, @EndDate) --Quarter WHEN @EndDatepart = 'qq' OR @EndDatePart = 'q' THEN DATEDIFF(qq, @StartDate, @EndDate) --Day of Year WHEN @EndDatepart = 'dy' OR @EndDatePart = 'y' THEN DATEDIFF(dy, @StartDate, @EndDate) --Hour WHEN @EndDatepart = 'hh' THEN DATEDIFF(hh, @StartDate, @EndDate) --Minute WHEN @EndDatepart = 'mi' OR @EndDatePart = 'n' THEN DATEDIFF(mi, @StartDate, @EndDate) --Second WHEN @EndDatepart = 'ss' OR @EndDatePart = 's' THEN DATEDIFF(s, @StartDate, @EndDate) --Millisecond WHEN @EndDatepart = 'ms' THEN DATEDIFF(ms, @StartDate, @EndDate) END )END |
|
|
|
|
|
|
|
|
Period