Solved

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

Posted on 2010-09-24
12
950 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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