Solved

SSRS 2005  Show Today and Last Day of previous month in report

Posted on 2010-09-24
12
955 Views
Last Modified: 2012-05-10
I want to show todays date and the last day date of the previous month in a report text box.

should look like this:

2010/09/24, 2010/08/31

How would you do ?
0
Comment
Question by:thayduck
[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
  • 6
  • 5
12 Comments
 
LVL 11

Accepted Solution

by:
aelliso3 earned 125 total points
ID: 33755835

See if this convers it ...

SELECT CONVERT(VARCHAR(10), GETDATE(),111) AS CurrentDate
, CONVERT(VARCHAR(10),(DATEADD(dd, - DAY(GETDATE()), GETDATE())),111) AS LastDayPrevMonth
0
 

Author Comment

by:thayduck
ID: 33756040
I want to do it using a text box expression without creating new flds in my query.

Can that be done ?
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33756161
Current Date
         =Globals!ExecutionTime Last Day of Previous Month
          =DATEADD("d",-(DATEPART("d",Globals!ExecutionTime)), Globals!ExecutionTime)
Then in the properties, set the Format to  ....       yyyy/M/d
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:thayduck
ID: 33756331
m/e date is showing like this:

8/31/2010 1:14:43 PM

need to be   2010/08/31
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33756364
that's what the last line was for ...

Then in the properties, set the Format to  ....       yyyy/M/d
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33756413
You can set it in the GUI part like this. Right click on the textbox, select TextBox Properties. Then select Number on the left hand side, select Custom on the list, and in custom properties, type in yyyy/MM/dd
DateCustomProperties.JPG
0
 

Author Comment

by:thayduck
ID: 33757019
Still having same issue
m/e date is showing like this:

8/31/2010 1:14:43 PM

need to be   2010/08/31  
 
 

ssrsformat-.doc
0
 
LVL 7

Expert Comment

by:GKalosky
ID: 33757084
You need to break this into 3 fields to be able to apply the formatting at the report level.  It will only apply the date format if all the data in the field is a date.  You currently have 2 dates and a comma as one long string.
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33757209
Actually ... you can convert it in the same textbox, but I was mistaken when I thought you had two different textbox's you were putting them into ... just apply the FORMAT command like below:
=FORMAT(Globals!ExecutionTime,"yyyy/MM/dd")
=FORMAT(DATEADD("d",-(DATEPART("d",Globals!ExecutionTime)), Globals!ExecutionTime),"yyyy/MM/dd")
 
0
 

Author Closing Comment

by:thayduck
ID: 33757295
aelliso3:

I have decided to use your first suggestion which works perfectly.

Thanks....
0
 

Author Comment

by:thayduck
ID: 33757342
aelliso3:
Did not realize you sent another solution. Your last solutio you sent works perfectly to, so I WILL use that one.

Again, Thank You.....
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33757382
The last one is probably the cleanest way ... glad it worked out ... thx
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
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 …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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