• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 960
  • Last Modified:

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

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
thayduck
Asked:
thayduck
  • 6
  • 5
1 Solution
 
aelliso3Commented:

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
 
thayduckAuthor Commented:
I want to do it using a text box expression without creating new flds in my query.

Can that be done ?
0
 
aelliso3Commented:
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
thayduckAuthor Commented:
m/e date is showing like this:

8/31/2010 1:14:43 PM

need to be   2010/08/31
0
 
aelliso3Commented:
that's what the last line was for ...

Then in the properties, set the Format to  ....       yyyy/M/d
0
 
aelliso3Commented:
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
 
thayduckAuthor Commented:
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
 
GKaloskyCommented:
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
 
aelliso3Commented:
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
 
thayduckAuthor Commented:
aelliso3:

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

Thanks....
0
 
thayduckAuthor Commented:
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
 
aelliso3Commented:
The last one is probably the cleanest way ... glad it worked out ... thx
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now