SSRS Report Parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

SSRS Report Parameter

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-06-12 : 16:37:19

All,
I have a report that needs to return a number of columns from a table based on 2 parameters [StartDate & EndDate [datetime]].

Here is my dataset
Select * from table1 where start_date between @StartDate and @Enddate.
In table1 [start_date datetime ,example value=[3/6/2008 6:41:47 PM]]

My problem is how do I convert the start_date to [3/6/2008] within my Dataset.
Something like the below.[I get an error when I do the below]
Select * from table1 where convert(varchar(10),start_date,111) between @StartDate and @Enddate.

The reason for doing this is because when I pick, StartDate as 01/01/2008 and EndDate as 1/31/2008.My report only return's values of 01/01/2008 to 01/30/2008.The items that falls on 1/31/2008 do not get displayed as the datasets does not consider time when comparing,but only the date part.

Do give me a suggestion,solution to this

.Thank You in advance!

mfeeney93
Starting Member

1 Post

Posted - 2008-06-13 : 08:55:23

I have successfully used the following:


declare @sDate nvarchar(10)
declare @eDate nvarchar(10)
select @sDate=convert(nvarchar(10),@StartDate,112)
select @eDate=convert(nvarchar(10),@EndDate,112)

select *
from
    table1
where
    convert(nvarchar(10),start_date,112) >= @sDate
    and convert(nvarchar(10),start_date,112) <= @eDate

I hope that helps. Mind you, I'm a relatively new to SQL Server, so this may not be the most efficient method.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-06-13 : 10:37:38

Thank You very much..It worked just fine for me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources