December 3, 2008 at 2:03 pm
If I am looking at the first day of a month, if I use the DATEADD function, I can add or subtract months or quarters from this date and end up on the first day of the resultant month or quarter. For example, I can add or subtract any number of months from 2/1/2008 and I will remain on the 1st. Only the month will change.
If I am on the last day of the month however, it doesn't work so well. If I add 1 month to 3/31/2008, I get 4/30/2008. So far, so good. But, if I add 1 month to 4/30/2008, I get 5/30/2008. What I would like is 5/31/2008.
Has anybody written a function that will allow me to jump from the last day of one month to the last day of some month in the future? The one way I can do this is add an extra month and subtract the days value of the result FROM the result and that puts me on the last day of the previous month. It looks something like this:
DECLARE @BaseDate CHAR(8), @TargetDate CHAR(8)
SET @BaseDate='20080229'
SET @TargetDate=CONVERT(CHAR,DATEADD(m,1,@BaseDate),112)
PRINT 'Wrong date result='+@TargetDate
PRINT '--------'
SET @TargetDate=CONVERT(CHAR,DATEADD(d,-1*DATEPART(dd,DATEADD(m,1+1,@BaseDate)),DATEADD(m,1+1,@BaseDate)),112)
PRINT 'Desired date result='+@TargetDate
December 3, 2008 at 2:07 pm
If you find a definition what is "last day of month" you'll get your solution.
_____________
Code for TallyGenerator
December 3, 2008 at 2:24 pm
Review the following:
Select dateadd(month, datediff(month, 0, current_timestamp), 0)
,dateadd(month, datediff(month, -1, current_timestamp), -1)
,dateadd(month, datediff(month, -1, current_timestamp) - 2, -1)
,dateadd(month, datediff(month, -1, current_timestamp) - 1, -1)
,dateadd(month, datediff(month, -1, current_timestamp) + 0, -1)
,dateadd(month, datediff(month, -1, current_timestamp) + 1, -1)
,dateadd(month, datediff(month, -1, current_timestamp) + 2, -1);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 3, 2008 at 2:28 pm
Here is some more code for you to review:
declare @InputDate datetime,
@Increment int;
set @InputDate = getdate();
set @Increment = 0; -- Find EOM for current month
select dateadd(mm, datediff(mm, 0, getdate()) + (1 + @Increment), 0) - 1
set @Increment = 1; -- find EOM for following month
select dateadd(mm, datediff(mm, 0, getdate()) + (1 + @Increment), 0) - 1
set @Increment = -1; -- find EOM for previous month
select dateadd(mm, datediff(mm, 0, getdate()) + (1 + @Increment), 0) - 1
December 3, 2008 at 2:44 pm
Or this:
declare @InputDate datetime,
@Increment int;
set @InputDate = getdate();
set @Increment = 0; -- Find EOM for current month
select dateadd(mm, datediff(mm, 0, @InputDate) + (1 + @Increment), -1)
set @Increment = 1; -- find EOM for following month
select dateadd(mm, datediff(mm, 0, @InputDate) + (1 + @Increment), -1)
set @Increment = -1; -- find EOM for previous month
select dateadd(mm, datediff(mm, 0, @InputDate) + (1 + @Increment), -1)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply