DSXchange

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.

Welcome aboard.

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

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.

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

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.

Where did the original question mention Oracle?

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

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

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

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

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.

I just figured out your opinion about Ireland.

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".