Solved

SSRS 2008 Express to get LAST DAY Of Month

Posted on 2011-09-19
9
541 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

831 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