last day of the month of a given date

Swaruparani · Post by **Swaruparani** » Wed May 20, 2009 11:57 pm

i will pass a date an input ,i need to get the last day of that month.

EX:if i will pass 2009-02-16 as input then my expected output should be 28(last day of FEB)

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Thu May 21, 2009 12:05 am

Welcome aboard.

This is a perfect example of something most easily resolved using a time "dimension" (lookup) table at day granularity.

priyadarshikunal · Post by **priyadarshikunal** » Thu May 21, 2009 3:34 am

ray.wurlod wrote:This is a perfect example of something most easily resolved using a time "dimension" (lookup) table at day granularity.

Yes, It is. But if its a straight forward requirement then transforming it to 1st of next month -1 day is not a bad idea.

dsedi · Post by **dsedi** » Thu May 21, 2009 8:48 am

Search is your friend

you can do it via oracle procedure too

CREATE FUNCTION dbo.LastMonthDay
( @Date datetime )
RETURNS datetime
AS
BEGIN
RETURN (CASE WHEN MONTH(@Date)= 12
THEN DATEADD(day, -1, CAST('01/01/' + STR(YEAR(@Date)+1) AS DateTime))
ELSE DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/01/' + STR(YEAR(@Date)) AS DateTime))
END)
END
GO

priyadarshikunal · Post by **priyadarshikunal** » Thu May 21, 2009 9:10 am

If you can use oracle query then you don't need a procedure to do the same. there is a function called Last_day() in oracle which gives you last day of the month without any effort.

ray.wurlod · Post by **ray.wurlod** » Thu May 21, 2009 4:27 pm

Where did the original question mention Oracle?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri May 22, 2009 3:00 am

Can do something like
endOfMonthDate = YYYY + INT(MM / 12) : MOD(MM, 12) + 1 : 01 - 1

priyadarshikunal · Post by **priyadarshikunal** » Fri May 22, 2009 4:30 am

ray.wurlod wrote:Where did the original question mention Oracle?

I was just replying to the post above suggesting to use a oracle procedure.

ray.wurlod · Post by **ray.wurlod** » Fri May 22, 2009 5:56 am

What if they're a DB2 shop? You've been in Ireland too long!

priyadarshikunal · Post by **priyadarshikunal** » Fri May 22, 2009 6:31 am

What if they're a DB2 shop?

Then they must have started searching for me.

You've been in Ireland too long!

Yes.
Something wrong with the language or nature of reply?
then I must start thinking on that.

priyadarshikunal · Post by **priyadarshikunal** » Fri May 22, 2009 6:47 am

I just figured out your opinion about Ireland.

ray.wurlod · Post by **ray.wurlod** » Fri May 22, 2009 4:04 pm

There is a very old joke, though many say it's true, that goes something like this. A visitor to Ireland asks directions to a particular attraction, and gets the reply "well, if I were going there, I wouldn't be starting from here".