LAST_DAY function in db2

[sshettar]

Hi All,

I have this field which needs to get the month end date of a given date field . i am populating a db2 table. I know db2 does have a function LAST_DAY which would give this result, i tried using this function on Advanced Query Tool , but i'm getting the following error

42884(-440)[IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "LAST_DAY" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884
(0.19 secs)

Do not know as to why i'm getting this error.

any help in achieving this task would be of great help

Thanks in advance

[Sainath.Srinivasan]

No authorized routine named "LAST_DAY" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

Appears issue with the arguments passed.

[chulett]

First person you should take this to, IMHO, is your DBA. That or Google for examples of the proper syntax.

[sshettar]

I did check with the DBA and he said that the version of DB2 we are using doesnt support the function LAST_DAY.
is there any other way to get the month end date of a given date ?

any helo is highly appreciated

Thanks in advance

[chulett]

Sorry, my helicopter is in the shop.

Check this thread: viewtopic.php?t=115051

[priyadarshikunal]

probably this topic can give you a good start

viewtopic.php?p=338077&highlight=#338077

apart from that you need to add 1 to the month and if its 12 add 1 to year and hard code 01 as month.

[Grace J.]

Try this, select (given_date +1 month -day(given_date) day) lastday from tablename ;

[laknar]

select DATE('given_date') +1 month -day(DATE('given_date')) as lastday from tablename

[sshettar]

Thanks Grace and laknar!!!!

the command worked for me .

Thanks you once again. i'm marking this topic as resolved