records with in a date range - 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

General SQL Server Forums

New to SQL Server Programming

records with in a date range

Author

Topic

manosgovind
Starting Member

12 Posts

Posted - 2014-06-11 : 05:02:31

HI there,

I have to find the records with in a paricular dates(from date and to date). In some cases @FromDate or @ToDate could be null. in the following query, when i am passing values @FromDate and @ToDate and execute the SP i am getting records which are not in the given range. can any one help me to find my mistake?

SELECT * FROM TABLE P WHERE
CONVERT(VARCHAR(10), P.[FromDate], 101) BETWEEN
CASE When @FromDate IS NULL Then CONVERT(VARCHAR(10),@minFromDate, 101)
ELSE CONVERT(VARCHAR(10), @FromDate, 101) END
AND CASE WHEN @ToDate IS NULL Then CONVERT(VARCHAR(10), @maxToDate, 101)
ELSE CONVERT(VARCHAR(10), @ToDate, 101) END
AND CONVERT(VARCHAR(10), P.[ToDate], 101) BETWEEN
CASE When @FromDate IS NULL Then CONVERT(VARCHAR(10), @minFromDate, 101)
ELSE CONVERT(VARCHAR(10), @FromDate, 101) END
AND CASE When @ToDate IS NULL Then CONVERT(VARCHAR(10),@maxToDate, 101)
ELSE CONVERT(VARCHAR(10), @ToDate, 101) END

I am using SQL 2008.

thanks in advance

chbala85
Starting Member

49 Posts

Posted - 2014-06-11 : 06:14:08

Give more clarification on this query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-11 : 06:57:59

There is a flaw in your query. When you convert the dates to strings, your sort order is not what you want.

SELECT	*
FROM	dbo.Table AS p
WHERE	p.FromDate BETWEEN ISNULL(@FromDate, @minFromDate) AND ISNULL(@ToDate, @maxToDate)
	AND p.ToDate BETWEEN ISNULL(@FromDate, @minFromDate) AND ISNULL(@ToDate, @maxToDate)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

manosgovind
Starting Member

12 Posts

Posted - 2014-06-11 : 07:42:32

I have two fields fromdate and todate in my table. from my report values for the parameters @fromdate and @ToDate are given(some times it could be null). if @fromdate is null and @todate has some value. i want to display the records from the date which is minimum(fromdate) in the table to the date which is given. Similarly if @ToDate is null and @fromDate is given, the data from the given date to the maximim(ToDate) in the table should be displayed. And if both are not null, the records should be with in the date range.

pls excuse me for my English

manosgovind
Starting Member

12 Posts

Posted - 2014-06-17 : 01:50:24

Hi SwePeso,

Sorry for the late reply.It works as intended.Thanks for the help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources