Solved

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

Posted on 2010-09-24
12
951 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Analysis of table use 7 54
SSRS Matrix Grand Total 2 58
create insert script based on records in a table 4 24
Find data in a column which is not in  a date format 29 35
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this article I will describe the Copy Database Wizard 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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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