stored procedure search condtion between two dates

Question

stored procedure search condtion between two dates

Arjun SreeVastsva

SSCertifiable

Points: 7137
More actions
May 31, 2010 at 5:58 am

#221684

hey i want the dyanamic stored procedure for searching products and search condition between two dates
like 'fromdate' beween 'todate' or date search condition like Time Period like 1 week,1 Month,2 Months,3 Moths 6 Months ..
can any one help me..

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please post table definitions, sample data and expected results as per http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 2

It would be much better if you keep the logic of 1 week, 1 Month, 6 Months, etc on the front end.

All you receive should be from date and to date, set by application on the basis of the selected span.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

naveena 78933 SSC Enthusiast Points: 101 More actions · Answer 3

SELECT id,Name, salary FROM Emp

WHERE fromdate >= '1-1-82' AND today <= '12-31-06'

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

naveena 78933 (6/2/2010)
SELECT id,Name, salary FROM Emp
WHERE fromdate >= '1-1-82' AND today <= '12-31-06'

Ummm... no... don't use two digit years and don't use the mm-dd-yy format for such things in this global economy. It's best to use the ISO format of yyyymmdd or, if you insist on easy human readability, use the yyyy-mm-dd format. It works everywhere.

But, I don't believe that's what the OP was and the <= should actually be just a < with and end date that is 1 greater than the desired date to pickup any time elements that will eventually appear.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 5

SELECT something

FROM somewhere

WHERE whenever

BETWEEN Now AND Then

Disclaimer: I am not at my SQL machine, so the syntax may not be perfect and I haven't been able to fully test it to your posted requirements.

HTH

Arjun SreeVastsva SSCertifiable Points: 7137 More actions · Answer 6

SELECT something

FROM somewhere

WHERE whenever

BETWEEN Now AND Then

Disclaimer: I am not at my SQL machine, so the syntax may not be perfect and I haven't been able to fully test it to your posted requirements.

HTH

Thanks but its is coming error like Convert is failed like that

Sahasam Ten Centuries Points: 1001 More actions · Answer 7

USE AdventureWorks;

Select EmployeeID, ContactID, Title, HireDate

From HumanResources.Employee

Where HireDate Between '1996-07-31' AND '2003-07-01'

Arjun SreeVastsva SSCertifiable Points: 7137 More actions · Answer 8

It is good...but Search Condition withing past 2 days or 1 week, 2 week, 1 month,three months...

i need dates condition to serach for past 1 day or past 1 week 2 weeks 1 month like

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

GilaMonster (5/31/2010)
Please post table definitions, sample data and expected results as per http://qa.sqlservercentral.com/articles/Best+Practices/61537/

I would also agree with Atif. The logic of intervals should remain on the front end and all SQL should get is two dates.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 10

Raju The Leader (6/2/2010)
Thanks but its is coming error like Convert is failed like that

Did you DECLARE Now and Then as Datetime? You should do, then SET them sooner rather than later...

sunil-677791 Newbie Points: 1 More actions · Answer 11

It is good...but Search Condition withing past 2 days or 1 week, 2 week, 1 month,three months...

i need dates condition to serach for past 1 day or past 1 week 2 weeks 1 month like

pass one more parameter in to store procedure suppose @filter where values day, week and month

1st declare @todate variable.

U should know wht r u passing

if u passing

1 days

Check

if @filter = 'day'

set @todate = Dateadd(day,noofdays,@fdate)

ELSE if @filter = 'WEEK'

set @todate = Dateadd(dw,noofWEEKS,@fdate)

ELSE IF @filter = 'MONTH'

set @todate = Dateadd(mm,noofMONTH,@fdate)

if u r passing week then

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

rjohal-500813 (6/2/2010)
SELECT something FROM somewhere WHERE whenever
BETWEEN Now AND Then
Disclaimer: I am not at my SQL machine, so the syntax may not be perfect and I haven't been able to fully test it to your posted requirements.
HTH

HTH? Nope... DBS (Death by SQL). If times are involved (and you should always plan on them even if not in the specs), you will miss a whole day. Read my previous post for the correct way to check for dates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 13

rjohal-500813 (6/3/2010)
Raju The Leader (6/2/2010)
Thanks but its is coming error like Convert is failed like that
Did you DECLARE Now and Then as Datetime? You should do, then SET them sooner rather than later...

Huh? I think people will appreciate simple answers more than exhibitions of your literary prowess. To be honest, I think your intention is to baffle the party rather than answer his question. I don't mean to offend; it's just what I think.

- arjun

https://sqlroadie.com/