Tuesday, November 9, 2010

Playing with 0 as Parameter in the Date function

I recently came to understand how using 0 in the month or day parameter of the date function can be pretty handy. I admit this is a basic idea to many but if you don't use it I figure this insight might get the wheels turning.

This example shows how to calculate the last date of the month from any supplied date.






Let ([

    // Put your date or date field here
    theDate = "10/30/2010";
    // Get last date of month base on theDate
    lastDateOfMonth = date ( month(theDate) + 1; 0 ; year(theDate))
];
    lastDateOfMonth
)

or this example which results in the last date of the year prior...



date ( 0+1; 0; 2010) // last date of year for year 2010

Notice the 0 and how it's behaving when used inside the date function. I realize grabbing the last date of any year is easy to calculate in other ways, it's not the most intriguing use case but I wanted to demonstrate the behavior of using 0 as month.
Good stuff. So I plan to play with 0, + and - in functions more often.

No comments:

Post a Comment