Re: AW: Need simple SQL to get the last day of month of previous month

This is similar to what Chuck Pence and I offered - for at least version 6.1 -

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

I rather prefer this way - it feels more direct then nested function calls - but that's a personal choice we each make, right?

The problem Glenn mentions would appear if a person did this -

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

As I mentioned, it's kind of like doing the half-adjust in the right place!

As my colleague and I were saying in regard to this, subtracting "1 months" will always get you some date in the previous month - never 2 months earlier. So then last_day will work there.

Much regards
Vern

On 10/22/2015 3:19 PM, Birgitta Hauser wrote:

If the current date is Feb. 2015, wouldn't that return Jan. 28?

No!
Because of the use of ADD_MONTHS!
If the start date (1st parameter) in ADD_MONTHS is a month end the newly calculated date is again a month end, independent whether the (month end) start date was in a month with 28, 29, 30 or 31 days.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"


-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Glenn Gundermann
Gesendet: Thursday, 22.10 2015 21:59
An: Midrange Systems Technical Discussion
Betreff: Re: Need simple SQL to get the last day of month of previous month

Hi Birgitta,

If the current date is Feb. 2015, wouldn't that return Jan. 28?

Would you not prefer:

Values(Last_Day(Add_Months(Current_Date, -1))) ;


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 22 October 2015 at 15:49, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Please Check the SQL Reference: The Add_Months and Last_Day scalar
functions.

Values(Add_Months(Last_Day(Current_Date), -1));

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag
von Hoteltravelfundotcom
Gesendet: Thursday, 22.10 2015 17:57
An: Midrange Systems Technical Discussion
Betreff: 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 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 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.