Solved

how to set date parts in Date and Time Functions dynamicly

Posted on 2004-08-11
7
355 Views
Last Modified: 2008-03-10
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.
0
Comment
Question by:lanetas
7 Comments
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
 

Author Comment

by:lanetas
Comment Utility
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
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
NEVER MIND:

Posted too quickly, the full DATEADD() expression can go in the THEN.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Actually, IMO, BillAn1's answer was better.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now