how to set date parts in Date and Time Functions dynamicly

I have a Table with the following structure

myTable:
datepart (varchar(4)) ,  timeIncrement (int)

I need to increment for each record, the current date based on the fields thah I have described above.

I had tryed this

select DATEADD(datepart , timeIncrement, GETDATE()) AS TheDateINeed
From myTable

however, I recive this message
'datepart' is not a recognized dateadd option.
lanetasAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
NEVER MIND:

Posted too quickly, the full DATEADD() expression can go in the THEN.
0
 
jdlambert1Commented:
Only way I could get it work is as dynamic sql:

DECLARE @sql nvarchar(888)
SELECT @sql = 'SELECT DATEADD(' + datepart + ',' + cast(timeIncrement as varchar(88)) + ', GETDATE()) AS TheDateINeed From myTable'
FROM myTable
EXEC sp_executesql @sql
0
 
lanetasAuthor Commented:
It does not work!

I runned it with this info:

myTable:
datepart      timeIncrement
dd      1
year      2
dd      3
      
      
and the result was:

Running dbo."__Tempo".

TheDateINeed            
-----------------------
2004-08-14 1:44:01 PM  
2004-08-14 1:44:01 PM  
2004-08-14 1:44:01 PM  
No more results.
(3 row(s) returned)
@RETURN_VALUE = 0
Finished running dbo."__Tempo".


There are not increments on the current date for each record!
It fix the last datepart      and timeIncrement in the select statement


2004-08-12 1:44:01 PM    (getdate() + 1 day)
2006-08-11 1:44:01 PM    (getdate() + 2 years)
2004-08-14 1:44:01 PM    (getdate() + 3 days)
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
jdlambert1Commented:
Okay, this will return one set of values, one for each record in myTable:

SET NOCOUNT ON
CREATE TABLE #DateINeed (TheDateINeed datetime NOT NULL)
DECLARE @sql nvarchar(888), @datepart varchar(4), @timeIncrement int

DECLARE Cur1 CURSOR FAST_FORWARD FOR
  SELECT datepart, timeincrement FROM myTable

OPEN Cur1
FETCH NEXT FROM Cur1 INTO @datepart, @timeIncrement
WHILE @@fetch_status = 0
BEGIN
  SELECT @sql = 'INSERT #DateINeed SELECT DATEADD(' + @datepart + ',' + cast(@timeIncrement as varchar(88)) + ', GETDATE())'
  EXEC sp_executesql @sql
  FETCH NEXT FROM Cur1 INTO  @datepart, @timeIncrement
END
CLOSE Cur1
DEALLOCATE Cur1

SELECT TheDateINeed FROM #DateINeed ORDER BY 1
DROP TABLE #DateINeed
0
 
BillAn1Commented:
try a select statement

select case datepart
              when  'yyyy' then DATEADD(yyyy, timeincrement, getdate())
              when  'yy' then DATEADD(yy, timeincrement, getdate())
              when  'qq' then DATEADD(qq, timeincrement, getdate())
              when  'q' then DATEADD(q, timeincrement, getdate())
              when  'mm' then DATEADD(mm, timeincrement, getdate())
              when  'dd' then DATEADD(dd, timeincrement, getdate())
              when  'dy' then DATEADD(dy, timeincrement, getdate())
              when  'd' then DATEADD(d, timeincrement, getdate())
              when  'hh' then DATEADD(hh, timeincrement, getdate())
              when  'mi' then DATEADD(mi, timeincrement, getdate())
              when  'mn' then DATEADD(mn, timeincrement, getdate())
              when  'ss' then DATEADD(ss, timeincrement, getdate())
end
From myTable
0
 
Scott PletcherSenior DBACommented:
If you have a limited number of dateparts, you could do something "ugly" like this:


SELECT DATEADD(YEAR, CASE WHEN datePart IN ('yy', 'year') THEN timeIncrement ELSE 0 END,
      DATEADD(MONTH, CASE WHEN datePart IN ('mm', 'month') THEN timeIncrement ELSE 0 END,
      DATEADD(DAY, CASE WHEN datePart = 'dd' THEN timeIncrement ELSE 0 END, GETDATE())))
0
 
Scott PletcherSenior DBACommented:
Actually, IMO, BillAn1's answer was better.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.