SQL DateTime logic for Last month report

Assuming the field you want to filter on is DateTime:

DateTime BETWEEN DATEADD(MONTH,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

Perfect answer!!! This is what i was looking for. Great!

Do you know how do i just print it to display what time it calculating ? I mean i want to test this code?

What about last 6 month? Just replace -1 with -6 ? Like following?

DateTime BETWEEN DATEADD(MONTH,-6,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND DATEADD(s,-6,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

If you are pulling APM data keep in mind that APM entries are based off of UTC time, so if you need data down to the hour or less use GETUTCDATE instead of GETDATE when working with APM information.

You shouldn't need to change the second -1, just the first one.

Thanks for info but why? its using UTC time? any special reason for this?

Hi,

I am wanting to send an email notification with a Report that will run on the 1st of each Month to show some of the Alerts from the previous Month.

Unfortunately for me the ${N=Generic;M=LocalMonthName} will only show the Current Month Name.

Work Around (Maybe); Set the time to run the report on the Last day of each Month before 23:59:00

But which Last day? February only has 28(29) Days, and the other Months have either 30 or 31 Days.

To your knowledge is there and alternate SWQL or SQL Command Line that I can use to get Last Month on the 1st of each Month when this Report runs?

Thank you In advance for your feedback and support.

Kind Regards,

Dan

DC4Networks

Hi dc4networks

Bit of an old thread here but I suspect what your looking for is:

DateTime > dateadd(mm,-1,getdate())

Hope that helps.