Dynamic datepart with DATEADD Function


  

 

CREATE TABLE [dbo].[ProcessMaster](
[ProcessID] [int] IDENTITY(1,1) NOT NULL,
[Frequency] [varchar](20) NULL,
[FrequencyQty] [int] NULL,
[LastStarted] [datetime] NULL)

insert into [ProcessMaster] (Frequency,FrequencyQty, LastStarted) 
values( 'dd', 1, '2017-06-15 08:16:20.587')
,('mi' ,5,'2010-06-22 11:12:00.537')
 
declare  @Frequency nvarchar(2), @FrequencyQty int, 
--more variables
@NextStart datetime , @ProcessID int=2
   
Select  @Frequency = Frequency, @FrequencyQty = FrequencyQty from ProcessMaster where ProcessID =  @ProcessID 

declare @sql nvarchar(2000) =N'Select  @NextStart=(Select dateadd('+@Frequency+',@FrequencyQty,LastStarted) from [dbo].[ProcessMaster] where ProcessID = @ProcessID )'
 

EXECUTE sp_executesql @sql, N'@NextStart datetime output,@Frequency char(2),@FrequencyQty int, @ProcessID int' ,@NextStart =@NextStart output , @Frequency =@Frequency , @FrequencyQty=@FrequencyQty , @ProcessID=@ProcessID
 
 
Select @NextStart 


drop table [ProcessMaster]


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/48a9fd46-3577-4b0b-9925-b9e67992b9a3/trying-to-dynamically-add-to-dates?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s