Scarlett20
asked on
How do I change SSRS date parameter hours, minutes and seconds?
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. ?
joevi
Are you using BETWEEN for the Dates in the Dataset Where Clause?
ASKER
Yes, the stored proc looks like this:
@fromdate datetime,
@todate datetime
and the select statement:
"where T7.servfrom between @fromdate and @todate"
@fromdate datetime,
@todate datetime
and the select statement:
"where T7.servfrom between @fromdate and @todate"
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"
DECLARE @fromdate datetime,
@todate datetime = GetDate();
and the select statement
"where T7.servfrom between @fromdate and @todate"
ASKER
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.
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.
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
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
ASKER
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.