is there a way that once a user enters a date parameter in that i can attach
a time to the end of the date.
eg.
user enters 11/11/05 in the parameter field. i want the date submitted to
the query to be 11/11/05 12:00:00 AM without them having to enter the time.In RS 2000 it automatically puts those times in. I had a case where I needed
it to be from 7 am to 7 am. So I set the parameter to text. Then you map the
query parameter to an expression.
= Parameters!MyParam.Value & " 7:00"
In 2005 there is a calendar control. If no time is selected it doesn't show
a time. If you have it as a date/time you can also use expressions to strip
the time portion. Look at VB date/time formating.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.matt.com> wrote in message
news:uX7uf2t5FHA.3276@.TK2MSFTNGP10.phx.gbl...
> is there a way that once a user enters a date parameter in that i can
> attach
> a time to the end of the date.
> eg.
> user enters 11/11/05 in the parameter field. i want the date submitted to
> the query to be 11/11/05 12:00:00 AM without them having to enter the
> time.
>
Showing posts with label enters. Show all posts
Showing posts with label enters. Show all posts
Thursday, March 22, 2012
Date Parameter - run through date not to date
Is there a way in reporting services to define your date parameters as
including the final date? Right now if someone enters 08/22/2005 for the
start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
is not what the users expect.
I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
Thanks for any help.
CoryIF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
YOU CAN CONVERT THE PARAMETER IN THE QUERY
SELECT *
FROM [TABLE]
WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
AND CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.EDATE,101)
THIS IS ONE OF MANY WAYS IT CAN BE DONE
P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
"Cory" wrote:
> Is there a way in reporting services to define your date parameters as
> including the final date? Right now if someone enters 08/22/2005 for the
> start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> is not what the users expect.
> I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> Thanks for any help.
> Cory|||OOPS THE >= ON THE @.EDATE SHOULD BE <= HEHE.. DUNNO HOW TO EDIT MY POST
"EsWallace" wrote:
> IF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
> NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
> YOU CAN CONVERT THE PARAMETER IN THE QUERY
> SELECT *
> FROM [TABLE]
> WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
> AND CONVERT(VARCHAR(11),[DATE],101) <= CONVERT(VARCHAR(11),@.EDATE,101)
> THIS IS ONE OF MANY WAYS IT CAN BE DONE
> P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
> "Cory" wrote:
> > Is there a way in reporting services to define your date parameters as
> > including the final date? Right now if someone enters 08/22/2005 for the
> > start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> > TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> > is not what the users expect.
> >
> > I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> >
> > Thanks for any help.
> >
> > Cory|||Thank you for your help! (I figured the less/greater than was backwards).
Thanks again.
--Cory
"EsWallace" wrote:
> OOPS THE >= ON THE @.EDATE SHOULD BE <= HEHE.. DUNNO HOW TO EDIT MY POST
> "EsWallace" wrote:
> > IF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
> > NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
> > YOU CAN CONVERT THE PARAMETER IN THE QUERY
> >
> > SELECT *
> > FROM [TABLE]
> > WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
> > AND CONVERT(VARCHAR(11),[DATE],101) <= CONVERT(VARCHAR(11),@.EDATE,101)
> >
> > THIS IS ONE OF MANY WAYS IT CAN BE DONE
> >
> > P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
> > "Cory" wrote:
> >
> > > Is there a way in reporting services to define your date parameters as
> > > including the final date? Right now if someone enters 08/22/2005 for the
> > > start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> > > TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> > > is not what the users expect.
> > >
> > > I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> > >
> > > Thanks for any help.
> > >
> > > Cory
including the final date? Right now if someone enters 08/22/2005 for the
start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
is not what the users expect.
I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
Thanks for any help.
CoryIF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
YOU CAN CONVERT THE PARAMETER IN THE QUERY
SELECT *
FROM [TABLE]
WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
AND CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.EDATE,101)
THIS IS ONE OF MANY WAYS IT CAN BE DONE
P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
"Cory" wrote:
> Is there a way in reporting services to define your date parameters as
> including the final date? Right now if someone enters 08/22/2005 for the
> start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> is not what the users expect.
> I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> Thanks for any help.
> Cory|||OOPS THE >= ON THE @.EDATE SHOULD BE <= HEHE.. DUNNO HOW TO EDIT MY POST
"EsWallace" wrote:
> IF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
> NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
> YOU CAN CONVERT THE PARAMETER IN THE QUERY
> SELECT *
> FROM [TABLE]
> WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
> AND CONVERT(VARCHAR(11),[DATE],101) <= CONVERT(VARCHAR(11),@.EDATE,101)
> THIS IS ONE OF MANY WAYS IT CAN BE DONE
> P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
> "Cory" wrote:
> > Is there a way in reporting services to define your date parameters as
> > including the final date? Right now if someone enters 08/22/2005 for the
> > start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> > TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> > is not what the users expect.
> >
> > I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> >
> > Thanks for any help.
> >
> > Cory|||Thank you for your help! (I figured the less/greater than was backwards).
Thanks again.
--Cory
"EsWallace" wrote:
> OOPS THE >= ON THE @.EDATE SHOULD BE <= HEHE.. DUNNO HOW TO EDIT MY POST
> "EsWallace" wrote:
> > IF THE START DATE PARAMETER IS NAMED SDATE AND THE END DATE PARAMETER IS
> > NAMED EDATE AND THE USER ENTERS EITHER AUG 30 2005 , 2005-08-30 OR 08/30/2005
> > YOU CAN CONVERT THE PARAMETER IN THE QUERY
> >
> > SELECT *
> > FROM [TABLE]
> > WHERE CONVERT(VARCHAR(11),[DATE],101) >= CONVERT(VARCHAR(11),@.SDATE,101)
> > AND CONVERT(VARCHAR(11),[DATE],101) <= CONVERT(VARCHAR(11),@.EDATE,101)
> >
> > THIS IS ONE OF MANY WAYS IT CAN BE DONE
> >
> > P.S. SORRY FOR THE CAPS TO LAZY TO HIT THE CAPS LOCK : )
> > "Cory" wrote:
> >
> > > Is there a way in reporting services to define your date parameters as
> > > including the final date? Right now if someone enters 08/22/2005 for the
> > > start date and 08/29/2005 for end date it will run from 08/22/2005 12:00 AM
> > > TO 08/29/2005 12:00 AM. This will not include results from the 29th - which
> > > is not what the users expect.
> > >
> > > I'd like for it to run through the date - so 08/29/2005 23:59 or 11:59 PM
> > >
> > > Thanks for any help.
> > >
> > > Cory
Subscribe to:
Posts (Atom)