Filtering By Date - 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

Transact-SQL (2005)

Filtering By Date

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 09:16:44

I would like to filter records between two Dates, having two parameters in my SP called:

@FromDate
@UntilDate

Now, I know how to do it, but not what is the fastest way to do this.
And also what if I only want to use @fromdate, or I only want to use @untildate

So I guess

Select * from table where
CONVERT(CHAR(10),datefield,120) =< @UntilDate
And
CONVERT(CHAR(10),datefield,120) >= FromDate

will not be good enough then...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 09:21:17

Try this

Select * from table where
(datefield>=@UntilDate or @UntilDate is null)
And
(datefield<dateadd(day,1,@FromDate) or @FromDate is null)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 09:25:38

No need to convert the datefield from the table - that is slowing down the query!
@FromDate and @UntilDate should be of type datetime.
setting them like this if the time part is to ignore:
set @FromDate='20091231'
set @UntilDate='20100427'

But maybe you can ask a bit more specific with structure, example data and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 09:37:07

Eventually this worked for me:

And
(@UntilDate is null or ([DateField] <= @UntilDate))
And
(@FromDate is null or ([DateField] >= @FromDate))

The only thing is I don't know how to insert a Null value in the SP.

If I do @FromDate = N'' it will not return anything

The secret to creativity is knowing how to hide your sources. (Einstein)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 09:40:20

@FromDate = NULL

No, you're never too old to Yak'n'Roll if you're too young to die.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2010-04-27 : 09:41:57

Haha, that worked...

The secret to creativity is knowing how to hide your sources. (Einstein)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 09:44:24

quote:
Originally posted by trouble2

Eventually this worked for me:

And
(@UntilDate is null or ([DateField] <= @UntilDate))
And
(@FromDate is null or ([DateField] >= @FromDate))

The only thing is I don't know how to insert a Null value in the SP.

If I do @FromDate = N'' it will not return anything

The secret to creativity is knowing how to hide your sources. (Einstein)

Does DateField have time too?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources