Query

Question

Post reply

Query

khandaresarang

Mr or Mrs. 500

Points: 532
More actions
December 22, 2009 at 2:01 am

#213836

Hi all,
I created a table in which there r n fields and in that two of them are date field i.e., startdate and enddate.
Now i want to write a query,to fetch the records from table which comes under startdate and enddate.
so,i need a help how to write the query.
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

What have you tried so far?

If nothing yet you might want to look into BETWEEN clause in BOL (BooksOnLine, the SQL Server help system).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

khandaresarang (12/22/2009)
Hi all,
I created a table in which there r n fields and in that two of them are date field i.e., startdate and enddate.
Now i want to write a query,to fetch the records from table which comes under startdate and enddate.
so,i need a help how to write the query.
Thanks in advance.

Can you provide the table creation script you used for this, and a series of INSERT statements which you may have used to populate the table? This will provide people with a starting point to help you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 3

If I understand you correctly we had a similar requirement where the input date range provided should overlap the StartDate and EndDate values in the records.

Say for example @StartDate and @EndDate are the input parameters and StartDate and EndDate are the field names then the query would be

SELECT columnlist FROM TableName WHERE StartDate <= @EndDate AND EndDate >= @StartDate

Thanks

Satish More

freeman.e.l SSC Veteran Points: 249 More actions · Answer 4

I agree with a slight modification if the EndDate is exclusive:

Logically you want all results from the table but only list results when the start date is before 'xx/xx/xxxx' and the end date is on/after 'xx/xx/xxxx'

SELECT columnlist FROM TableName WHERE StartDate < @EndDate AND EndDate >= @StartDate

You can do the following for a specific date:

select columnlist FROM TableName WHERE StartDate <= @TargetDate and @TargetDate < EndDate

geervani SSC Enthusiast Points: 146 More actions · Answer 5

U can use Between clause or datediff will be best i feel because between will not include the startdate and enddate.

But i am not understanding you question. Each record in the table has startdate and enddate right? do you want retrive all the records with common start and enddate?

Or if you have a want to retrive records in a perticular date range than u can use

select * from [tablename] where datediff(d,@startdate, start_date) >= 0 and datediff(d,@enddate, end_date) <= 0

here start_date and end_date are column names

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 6

select * from [tablename] where start_date >= @startdate and

end_date <=@enddate

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

geervani (1/5/2010)
U can use Between clause or datediff will be best i feel because between will not include the startdate and enddate.
But i am not understanding you question. Each record in the table has startdate and enddate right? do you want retrive all the records with common start and enddate?
Or if you have a want to retrive records in a perticular date range than u can use
select * from [tablename] where datediff(d,@startdate, start_date) >= 0 and datediff(d,@enddate, end_date) <= 0
here start_date and end_date are column names

Using column names inside a function should be avoided as it will not utilize the index.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Agree I must with Chris. Help it would if OP would provide table DDL, sample data, and expected results.