MTD Default Parameters

Question

MTD Default Parameters

mbrady5

SSCrazy

Points: 2835
More actions
March 1, 2016 at 10:03 am

#316477

Hello,
I'm looking for some MTD Default parameters. I am always getting hung up on the 1st day of the month. My parameter now (if run today) starts on 3/1 and ends on 2/29. Obviously that just wont work. Thanks for any "bullet proof" suggestions.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I don't get what MTD is or exactly what you are trying to do.

If you are looking for a default parameter value of the first day of the month and last day of the month you can do it in SQL (then use that SQL in a data set, then reference the dataset)

The SQL:

DECLARE @date date = getdate()+10;

SELECT

FirstDayOfMonth = DATEADD(DAY, -(DAY(@date)-1), @date),

LastDayOfMonth1 = EOMONTH(@date), -- if you are using 2012+

LastDayOfMonth2 = DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, -(DAY(@date)-1), @date))) -- pre 2012

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pietlinden SSC Guru Points: 63509 More actions · Answer 2

pietlinden

SSC Guru

Points: 63509

March 1, 2016 at 10:19 am

#1862151

This[/url] should be a good starting point.

Spiff SSCertifiable Points: 7826 More actions · Answer 3

Alan, if you use the pre /post 2012 last day of month calculations you posted you get different answers.

This is the problem I have with EOMONTH() is that it returns the date at midnight (I get why, but find it awkward to use).

E.g. 29/02/2016 00:00:00

So if you are comparing to a datetime anything with a later time on that day could be missed.

E.g. 29/02/2016 11:30:00

So if you need to complete month (including the last day) you need to do:

WHERE MyDate < DATEADD(DAY,1,EOMONTH(GETDATE())

I much prefer using the routines in Piet's link which as a plus are also "version agnostic".

select dateadd(mm, datediff(mm, 0, GETDATE()) + 1, 0) -- Beginning of next month

select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month

mbrady5 SSCrazy Points: 2835 More actions · Answer 4

Thanks for the quick responses.

Here is what I ended up with.

The issue I was having is with the default end date. For every date other that the 1st, it worked great but on the first, it always put the prior day as the end date.

This works;

=IIF(DATEPART(DateInterval.Day,Today())=1,Today(),DATEADD(DateInterval.Day,-1,Today()))