Solved

SSRS 2008 Express to get LAST DAY Of Month

Posted on 2011-09-19
9
546 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

696 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