I think GETDATE() is a SQL Server function to get current date. DATEADD and DATEDIFF are also not part of DB2 on IBM i. MONTH is a function that requires a parameter that is some kind of DATE/TIME value or character representation.

Now at least at 7.1 we have the following - and 7.2 does not have the ones in the OP's statement

select last_day(current date - 1 months) from sysibm/sysdummy1

From what I can tell, LAST_DAY came out in 6.1

There's no FIRST_DAY bit the above example could get it - just put "+ 1 days" in like this -

select last_day(current date - 1 months) + 1 days from sysibm/sysdummy1

Remember always to subtract the months first - just like half-adjust stuff, right? :D

HTH
Vern

On 10/22/2015 11:09 AM, paultherrien@xxxxxxxxxxxxxxxxxx wrote:
When I try running this...
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) from
sysibm.sysdummy1;
I get the error ... Message: [SQL0204] GETDATE in *LIBL type *N not found.
Cause . . . . . : GETDATE in *LIBL type *N was not found.

If you try running just the statement ...
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
you get the error regarding <END-OF-STATEMENT> -

This is not a complete statement.

We are on V7R2.

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Hoteltravelfundotcom
Sent: Thursday, October 22, 2015 11:57 AM
To: Midrange Systems Technical Discussion
Subject: Need simple SQL to get the last day of month of previous month

HI this code is not working on i.
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

error Message: Token <END-OF-STATEMENT> was not valid. Valid tokens: + - AS
<IDENTI

What would be the syntax for such a select statement?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].