Conversion query

I have 2 date variables passed into my store procedure as follows
@.StartDate datetime,
@.EndDate datetime
however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
') AND (DateEntered <= ' + @.EndDate + ')')
Any suggestions would be welcome.
ThanksYou have to explictly cast the datetime values to characters like so
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= '
+ Cast(@.StartDate As VarChar(20))
+ ') AND (DateEntered <= ' + Cast(@.EndDate As VarChar(20)) + ')')
Thomas
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@.microsoft.com...
>I have 2 date variables passed into my store procedure as follows
> @.StartDate datetime,
> @.EndDate datetime
> however I am getting a conversion error when running the command below whi
ch
> says "Syntax error converting datetime from character string"
> PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
> ') AND (DateEntered <= ' + @.EndDate + ')')
> Any suggestions would be welcome.
> Thanks

Control Date Range in Rpt Svc

lHi,
I have two date parameters (Start Date and End Date) in one report and it
alows users input startdate and enddate. But I don't want users execute
reports more than 5 days from the start date. How do I limit it before the
reprot gets executed? Thanks.
ChuckProbably the easiest way to control this would be in the report parameters.
Instead of allowing them to enter a start date and end date allow them to
enter one of the dates, then set the second parameter as the number of days
to include in the report and set a drop down for those values, 1-5.
For instance, if you want them to be able to enter the end date and create a
report for the previous five days you wuold set your parameters up like this.
Parameter Name: @.EndDate
Type: Date/Time
Parameter Name: @.StartDate
Type: Date/Time
In the Report Parameters dialog for StartDate set the available values as:
Label Value
1 day DateAdd(day, -1, @.EndDate)
2 days DateAdd(day, -2, @.EndDate)
ect, ect...
This should allow them to select any ending date and from 1 to 5 days
previous for the start date.|||Hi JHoward,
Thank you for getting back to me. This is one solution. However, report
users does not like the format because they have to add days into start date
to figure out the End Date. Is there a way that if it is more than 5 days,
it will bring up an alert message and will NOT execute the report even though
a user click 'View Report'? Thanks.
Chuck
"JHoward" wrote:
> Probably the easiest way to control this would be in the report parameters.
> Instead of allowing them to enter a start date and end date allow them to
> enter one of the dates, then set the second parameter as the number of days
> to include in the report and set a drop down for those values, 1-5.
> For instance, if you want them to be able to enter the end date and create a
> report for the previous five days you wuold set your parameters up like this.
> Parameter Name: @.EndDate
> Type: Date/Time
>
> Parameter Name: @.StartDate
> Type: Date/Time
> In the Report Parameters dialog for StartDate set the available values as:
> Label Value
> 1 day DateAdd(day, -1, @.EndDate)
> 2 days DateAdd(day, -2, @.EndDate)
> ect, ect...
> This should allow them to select any ending date and from 1 to 5 days
> previous for the start date.
>
>
>