Solved

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

Posted on 2010-09-24
12
953 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
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.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query 107 85
convert null in sql server 12 57
Re-appearing SQL Server Agent jobs 7 42
Are triggers slow? 7 22
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

679 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