Finding End of Month -- sql server reporting services

sql server reporting services : Finding End of Month

Mike Harbinger
12/21/2005 1:26:06 PM

Does anyone have a VB function they could pass along for
converting a date to the end of the last month?

I have a report that will be run for the period ending the prior
month and want the parameter date to default as follows:
Assum they are running it on 12/21, date should default to 11/30/2005

Thanks!

Asher_N
12/21/2005 1:45:52 PM

"Mike Harbinger" <MikeH@Cybervillage.net> wrote in news:u#uZqUnBGHA.516
@TK2MSFTNGP15.phx.gbl:

[quoted text, click to view]

Mike Harbinger
12/21/2005 2:20:48 PM

Outstanding, many thanks!

[quoted text, click to view]

Mike Harbinger
12/21/2005 2:30:27 PM

Asher

I am using timedate format; can it be set to the end of the month 11:59 pm?

[quoted text, click to view]

Mike Harbinger
12/21/2005 5:39:11 PM

William

Thanks for your reply but I an still on RS 2000

[quoted text, click to view]

William Stacey [MVP]
12/21/2005 7:51:56 PM

If your using 2005, you use my misc date project at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
hour:minute:second:millisecond of that day (i.e. adding one more ms would
move the date to start of next day.)

--
William Stacey [MVP]

[quoted text, click to view]

William Stacey [MVP]
12/22/2005 12:06:41 AM

I missed the VB function need. Here is a c# method you can convert:

public static DateTime GetEndOfMonth(DateTime date)
{
int daysInMonth = DateTime.DaysInMonth(date.Year, date.Month);
return new DateTime(date.Year, date.Month, daysInMonth, 23, 59,
59, 999);
}

private void button9_Click(object sender, EventArgs e)
{
DateTime date = DateTime.Parse("12/22/2005"); // Any date.
DateTime lastMth = date.AddMonths(-1);
DateTime endOfLast = GetEndOfMonth(lastMth);
Console.WriteLine("End of Last Month:" + endOfLast);
}

--
William Stacey [MVP]

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn