SSRS 2008 Express to get LAST DAY Of Month

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.
Marius0188Asked:
Who is Participating?
 
edlunadConnect With a Mentor Commented:
This should give you what you need...

=DateAdd("d",-1,DateSerial(year(Fields!Date_Floor.Value),month(Fields!Date_Floor.Value)+1,1))
0
 
Marius0188Author Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Marius0188Author Commented:
Thanks for the help. Looks like yours is working :)
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
edlunadCommented:
This is a SSRS expression ("MS SQL Reporting" Area)
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
0
 
edlunadCommented:
This question belons to three zones...

C# Programming Language, MS SQL Reporting, SQL Server 2008
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.