Solved

SSRS 2008 Express to get LAST DAY Of Month

Posted on 2011-09-19
9
543 Views
Last Modified: 2012-05-12
Good day,

I need an expression to get me the LAST DAY of the month for a given date.
What I already have an is nearly working is the following expression:
=CDATE(CSTR(DATEPART(DATEINTERVAL.YEAR, Fields!Date_Floor.Value)) & "/" & CSTR(DATEPART(DATEINTERVAL.MONTH, Fields!Date_Floor.Value)) & "/" & CSTR(DATEDIFF(DATEINTERVAL.DAY, Fields!Date_Floor.Value, DATEADD(DATEINTERVAL.MONTH, 1, Fields!Date_Floor.Value))))

The above expression works nearly fine except if the input date is already the last day of a month then it seem to subtract one day... For example:
If Input date is: 2011-05-31 then the expression returns: 2011-05-31 which is wrong. It must return 2011-05-31 as the last the of May (05).

Please help me fix this expression.

Kind regards,
Marius.
0
Comment
Question by:Marius0188
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 

Author Comment

by:Marius0188
ID: 36560396
Sorry i made a mistake. When the input date is 2011-05-31 then the above expression  returns: 2011-05-30 which is wrong. It must return 2011-05-31.

But this only happens if the input date is already the last day of the month.
Please help fix this :)

Thanks.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36560423
The last day of a month is the day prior to the first day of the next month.  The first day of a month is always 1.
0
 
LVL 9

Accepted Solution

by:
edlunad earned 500 total points
ID: 36561566
This should give you what you need...

=DateAdd("d",-1,DateSerial(year(Fields!Date_Floor.Value),month(Fields!Date_Floor.Value)+1,1))
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Marius0188
ID: 36564982
Thanks for the help. Looks like yours is working :)
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36565155
Test it thoroughly - it looks like a rather poor implementation (= buggy) of my suggestion.

ps.  It doesn't work at all under both SQL Server and C# (the question areas) by me.
0
 
LVL 9

Expert Comment

by:edlunad
ID: 36567411
This is a SSRS expression ("MS SQL Reporting" Area)
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36567553
0
 
LVL 9

Expert Comment

by:edlunad
ID: 36567567
This question belons to three zones...

C# Programming Language, MS SQL Reporting, SQL Server 2008
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36567595
OK, so it doesn't compile/run in 2 of the 3 areas.  (I'm not saying you are at fault, just a wierd choice of areas to post the question too).


Nevertheless there looks to be a bit missing to make it work correctly in all cases but as I said I can't test that exact code because it is in the 1 of the 3 areas I don't have good knowledge of.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question