Highlighted
Super Contributor.. Super Contributor..
Super Contributor..
1113 views

how to get End of month for reporting

Jump to solution

Dear Experts , 

i'm doing a report for incidents  opened from the begining of the current month till the end of the current month 

i now that there are a function now named startofmonth() . is there another function that i can get the date of end the current month 

i tried endofmonth()  and startofmonth()+30 00:00:00 and it did not work 

any ideas ?

Thanks and Best Regards
------------------------------
Amr Salah
0 Likes
1 Solution

Accepted Solutions
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: how to get End of month for reporting

Jump to solution

Since you said "end of the current month", why do you need to have an end time in your query? If you are always going to be running this in the month for which it applies, you only need to filter on 'after' and can ignore 'before'.

That being said, this should do the trick:

open.time>startofmonth() and month(open.time)<month(tod())+1 and year(open.time)=year(tod())

Keep in mind, that's going to show you January's tickets if you run the query in January. If you want to run a query in February to see January's tickets, that changes your question completely.

View solution in original post

0 Likes
5 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: how to get End of month for reporting

Jump to solution

You could try this. I found this in the 9.5 online help.

Last full month
The first day of last month to the last day of last month
DateSerial (Year(d), Month(d) - 1, 1) To DateSerial (Year(d), Month(d), 1 - 1)

0 Likes
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: how to get End of month for reporting

Jump to solution

Since you said "end of the current month", why do you need to have an end time in your query? If you are always going to be running this in the month for which it applies, you only need to filter on 'after' and can ignore 'before'.

That being said, this should do the trick:

open.time>startofmonth() and month(open.time)<month(tod())+1 and year(open.time)=year(tod())

Keep in mind, that's going to show you January's tickets if you run the query in January. If you want to run a query in February to see January's tickets, that changes your question completely.

View solution in original post

0 Likes
Highlighted
Super Contributor.. Super Contributor..
Super Contributor..

Re: how to get End of month for reporting

Jump to solution

thank you 🙂 it works 

Thanks and Best Regards
------------------------------
Amr Salah
0 Likes
Highlighted
Outstanding Contributor.. Outstanding Contributor..
Outstanding Contributor..

Re: how to get End of month for reporting

Jump to solution

@Amr_Salah wrote:

thank you 🙂 it works 


Glad to help!

P.S. Kudos are always appreciated. 😉

0 Likes
Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: how to get End of month for reporting

Jump to solution
Hi AChandler,
Can you please let me know what is the query if I need to return the "Last Full Month" records?
Let's say we are in September & I want to pull all the data from August?
Thanks
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.