MTD Default Parameters

  • mbrady5

    SSCrazy

    Points: 2835

    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.

  • Alan Burstein

    SSC Guru

    Points: 61074

    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: 62669

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

  • Spiff

    SSCertifiable

    Points: 7762

    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

    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()))

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

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