lanetas
asked on
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.
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.
ASKER
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)
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)
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
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
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
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
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())))
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())))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually, IMO, BillAn1's answer was better.
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