Can I calculate the end of the month, 45 days from now?

[Irishiii75]

I need to know if this can be done
Calculate the last day of the month for a calculated date
(I want to calculate the end of the month, 45 days from today.

SQL version 8.00

I have this in a select statement

CONVERT (CHAR (8), DATEADD (dd,45, Getdate()),112) as contend

This produces 20020607, when run today.

Can I add anything to it to produce 20020630 (in this case).

If it is run on May 2, it would produce 20020615 and I want to finally produce 20020630.

If it is run on May 22, it would produce 20020705 and I want to finally produce 20020731.

Thanks for any input

 

[MarkSweetland]

You can replace the @Run_date in the select statement with the dateadd(dd, 45, getdate()) statement. I left it as a variable for testing...

declare @Run_date smalldatetime
set @Run_Date = dateadd(dd, 45, '05/22/2002')
select  convert(char(8), dateadd(dd, -1,
	convert(smalldatetime, convert(varchar(2), (datepart(mm,@Run_date))+1)+'/1/'+convert(char(4),datepart(yyyy,@Run_Date)))
),112) as contend

contend  
-------- 
20020731

Mark

 

[bperry]

I like Mark's suggestion.
This is just another variation on his theme:

Select Convert(char(8),dateadd(mm,1, dateadd(day,45,getdate())) - Day(dateadd(mm,1,dateadd(day,45,getDate()))),112)
as contend


But one other thing you might want to consider: if you are on SQL 2000, this is an ideal spot to create a UDF (user defined function), so that you can centralize all that ugly code, and call it from multiple locations. Optionally, you could also pass it the date and DaysToAdd (as I show below), making the function more flexible.

You would create the UDF like this:
--------------------
CREATE FUNCTION EOM (@inDate datetime, @inDays int)
RETURNS char(8) AS
BEGIN
return Convert(char(8),dateadd(mm,1, dateadd(day,@inDays,@inDate))
- Day(dateadd(mm,1,dateadd(day,@inDays,@inDate))),112)

END
--------------------
And then you could call the UDF like this:

select dbo.EOM(getdate(),45) as contend
-- or --
select dbo.EOM('05/22/2002',100) as contend


rgrds, etc
bperry

 

[bperry]

My apologies, let me correct a minor mis-statement I made earlier.
When describing the UDF, I said that you could 'optionally' pass it the date, to make it more flexible. Actually, in this particular situation we MUST pass it the date, because the GETDATE() function is not allowed inside a UDF (believe it or not). Since the UDF can't obtain the value of GETDATE() on its own, we must pass it in as a parameter as I show in the example.

Also, I now notice that you indeed are on SQL 2000 (i.e. SQL version 8.00)

rgrds, etc
bperry

 

[Irishiii75]

Thanks to both Mark and bperry for your solutions.

I was able to use Mark's in my current need and it worked perfectly.

I will be able to use pberry's solution in the future.