Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I change SSRS date parameter hours, minutes and seconds?

Posted on 2010-11-26
10
Medium Priority
?
2,295 Views
Last Modified: 2012-05-10
My report has fromdate and todate params.  These are both datetime datatypes and RS provides my users with the little calenders to select their dates.  The problem is with the default setting for hours, minutes and seconds. The fromdate is fine because it defaults to 00:00:00.  But the todate also defaults to 00:00:00 - this causes the report to miss the entire ending day.  How can I change the todate hours, minutes and seconds to 23:59:59.  ?
0
Comment
Question by:Scarlett20
[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
  • 4
  • 3
  • 3
10 Comments
 
LVL 4

Expert Comment

by:joevi
ID: 34219527
Are you using BETWEEN for the Dates in the Dataset Where Clause?
0
 

Author Comment

by:Scarlett20
ID: 34219639
Yes, the stored proc looks like this:

@fromdate datetime,  
@todate datetime
 

and the select statement:
"where T7.servfrom between @fromdate and @todate"
0
 
LVL 10

Expert Comment

by:Mez4343
ID: 34219730
Wouldnt it make sense that the @toDate variable should default to current system date or some date relevant to the data you are running the report for?

DECLARE @fromdate datetime,  
@todate datetime = GetDate();

and the select statement
"where T7.servfrom between @fromdate and @todate"
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:Scarlett20
ID: 34219757
This particular report is for previous date ranges, so the todate will never be the current date.

Here's a look at the report header:
Productivity Report for month/s:
4/1/2010 12:00:00 AM thru 7/1/2010 12:00:00 AM

I really wanted data from 4/1/2010 12:00:00 thru 6/30/2010 23:59:59.  Because of the default, I added a day to the todate making it 7/1.
0
 
LVL 10

Expert Comment

by:Mez4343
ID: 34219807
ok you have a relevant date for todate but you need to subtract 1 second. Try DATEADD() function.

DECLARE @fromdate datetime = '7-1-2010 12:00:00 AM'  
DECLARE @todate datetime
SELECT @todate = dateadd(s,-1,@fromdate)
SELECT @todate

http://msdn.microsoft.com/en-us/library/ms186819(SQL.90).aspx
0
 

Author Comment

by:Scarlett20
ID: 34219820
sorry, but I don't want the users to add 1 day to the 'todate' as I did.  so instead of the users entering 7/1/2010, I want them to enter 6/30/2010 - and have the hours, minutes and seconds default to 23:59:59.
0
 
LVL 4

Expert Comment

by:joevi
ID: 34219828
The Evaluation of the between expression will work with the dates converted to text (a string). You'll lose the 'calendars' in the RS list box if you change the datatype to text so you may want to try changing the SP variables to nvarchar datatypes and pass strings (CAST the date parameters as nvarchar) from RS
0
 
LVL 10

Accepted Solution

by:
Mez4343 earned 750 total points
ID: 34219849
Cast the User input date to string and then force the time..

DECLARE @userdate datetime = '6-30-2010 00:00:00 AM'  
DECLARE @todate datetime
SET @todate = CAST(DATEPART(yyyy, @userdate) AS varchar) + '-' +
                     CAST(DATEPART(mm, @userdate) AS varchar) + '-' +
      CAST(DATEPART(dd, @userdate) AS varchar) + ' 23:59:59'
SELECT @todate
0
 
LVL 4

Assisted Solution

by:joevi
joevi earned 750 total points
ID: 34219971
You do NOT need to do all that string concatenation and/or datemath stuff. The dates are already stored as XX/XX/XXXX 00:00:00.

Simply
1) In SQL Server:Change the datatypes for your two SP variables from datetime to nvarchar(10)
2) In Reporting Services: Change the Parameter configurations in your (Stored Procedure) Dataset to:
=FormatDateTime(Parameters!fromdate.Value.ToString)
=FormatDateTime(Parameters!todatedate.Value.ToString)

*assuming your parameters are named fromdate/todate
0
 

Author Closing Comment

by:Scarlett20
ID: 34241691
Hard to know which of you guys/girls had the better solution.  That's because I found my source informix db only had the date (that I queried)  stored as datatype 'date' and not 'datetime'.   It loaded into our sql warehouse as datetime, but of course the times defaulted to all zeroes.   thanks to both of you - I really appreciate your help.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 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