Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
skoodog
Starting Member
5 Posts |
 Posted - 2011-12-19 : 20:17:35
|
| Example: My declared value @mydate = 10/1/2010. My reference variable is 4/27/09. What expression should I use to have the output = 10/27/10?I've been able to do this through a number of ill-advised nested statements, but I need some sort of exception to fix cases where I hit non-real dates, such as if @mydate = 11/1/10 and my reference is 10/31/10 (output can't be 11/31 as it's not a real date.Hopefully that all makes sense - thanks in advance! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-12-19 : 20:31:20
|
The following query would work for your example:DECLARE @mydate DATETIME = '10/1/2010';DECLARE @refDate DATETIME = '4/27/2009';SELECT DATEADD( dd, DAY(@refDate)-1, @mydate); But I suspect there is more to your question. For example:a) Would @myDate be always the first of the month? For example, if @myDate is 10/15/2010, do you still want to get 10/27/2010, or do you want the result to be offset by another 15 days?b) What do you want to happen if @refDate is 4/30/2009 and @mydate is 2/1/2010? |
 |
|
|
skoodog
Starting Member
5 Posts |
Posted - 2011-12-20 : 02:40:44
|
quote:
Originally posted by sunitabeck
The following query would work for your example:DECLARE @mydate DATETIME = '10/1/2010';DECLARE @refDate DATETIME = '4/27/2009';SELECT DATEADD( dd, DAY(@refDate)-1, @mydate); But I suspect there is more to your question. For example:a) Would @myDate be always the first of the month? For example, if @myDate is 10/15/2010, do you still want to get 10/27/2010, or do you want the result to be offset by another 15 days?b) What do you want to happen if @refDate is 4/30/2009 and @mydate is 2/1/2010?
Correct - my reference date is part of my data set - essentially I want to calculate that month's date (using the declared variable as the referenced month) with that day based on this reference date as a monthly anniversary date, with exceptions pulling the prior real date where the exceptions occur (like the aforementioned 31st anniversary date). |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
HELP! Output Date in reference to other date