Dateadd - Is dynamic datepart possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Dateadd - Is dynamic datepart possible?

Author

Topic

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-10-23 : 04:57:37

I am trying to pass in a dynamic datepart to the dateadd function. I'd like to do this


SET @StartDate = '01/01/2006'
SET @PeriodLetter = 'm'
SET @Period = 12
dateadd(@PeriodLetter,@Period,@StartDate)

Is this possible or another approach? I have an enum on the code side that specifies the period type but this just stored as a number in the db. I am just passing the PeriodLetter in as a parameter but it seems that dateadd can only support hardcoded Periodtypes!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 05:00:24

[code]

SET @StartDate = '01/01/2006'
SET @PeriodLetter = 'm'
SET @Period = 12

SELECT CASE
WHEN @PeriodLetter = 'm' THEN DATEADD(month, @Period, @StartDate)
WHEN @PeriodLetter = 'd' THEN DATEADD(day, @Period, @StartDate)
WHEN @PeriodLetter = 'y' THEN DATEADD(year, @Period, @StartDate)
END[/code]

Peter Larsson
Helsingborg, Sweden

FruitBatInShades
Yak Posting Veteran

51 Posts

Posted - 2006-10-23 : 05:11:53

Thanks Peter, I thought about using CASE but just wondered if there was a cleaner technique. Code side most date functions accept dateparts as strings, just seems wierd that T-SQL doesn't :(

USE fbisMasterDev

DECLARE @SubType smallint
DECLARE @SubID bigint
DECLARE @Roles varchar(50)
DECLARE @UserRoles varchar(50)
/* This need to be parameters */
DECLARE @UserID bigint
DECLARE @StartDate DateTime
DECLARE @PeriodLetter varchar(4)
DECLARE @Period smallint

SET @UserID = 1
SET @StartDate = '01/01/2006'
SET @PeriodLetter = 'm'

/* Get values we need from subscriptions */
SELECT     @SubType = SubscriptionType, @SubID = UniqueID, @Roles = Roles, @Period = RecurringPeriod
FROM         Subscriptions
WHERE     (UniqueID = 1);

/* Add record to user_Subscriptions */
INSERT INTO User_Subscriptions
(
	UserID,
	SubscriptionID,
	DateAdded,
	Expires,
	SubscriptionType,
	Active
)
VALUES
(
	@UserID,
	@SubID,
	@StartDate,
	dateadd(@PeriodLetter,@Period,@StartDate),
	@SubType,
	1
);

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-23 : 05:22:51

you can also write your own function that pass in string and perform the case when inside the function

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources