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?

[Webinar] Streamline your web hosting managementRegister Today

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
All Courses

From novice to tech pro — start learning today.