Solved

SSRS 2008 Express to get LAST DAY Of Month

Posted on 2011-09-19
9
542 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
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Disable TLS1.0 on Win 2012 server 7 55
C# XML Get Values 4 33
Using/Import statement in code doesn't see reference of dll just added. 3 25
C# LINQ 5 18
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

680 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