how do I simply date range query?

Greetings SQL Gurus,

I currently have the following:

sql="select * from mytable where id = "&givenID& "  " & _
" and startDate >'1/1/1900' and startDate < '"&givenDate&" ' " & _
" and endDate >' " &givenDate& " ' " & _
" and endDate < ' " &currentDate& " ' " 

On the webform, it is setup like this:

StartDate
   From:     1/1/2009                          To:   12/17/2009
EndDate
   From:   10/1/2000                          To: 12/17/2009

From for either StartDate or EndDate could be any date between 1/1/1900 and currentdate and in our example, today's date (which is currentdate) is 12/17/2009

Same with To for either StartDate and EndDate

Here is my question:

As you can see, I am using 4 parameters from, from, to, to)

Is it possible to just use:

Date:

StartDate:  1/1/900 (or whatever date range I wish to use)      EndDate: 12/17/2009 or whatever date range I wish to use?

Is this possible?
                 
Many thanks in advance
 
simflexAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hope you meant by this:

sql="select * from mytable where id = "&givenID& "  " & _
" and startDate >'1/1/1900' " & _
" and endDate <= isnull(' " &givenDate&, &currentDate&) " 

Kindly correct syntax mistakes..
And I meant this query..

SELECT * from mytable
where id = givenid
and startdate > '1/1/1900'
and enddate <= isnull(givenDate , getdate())
0
 
simflexAuthor Commented:
Thanks very very much rrjegan17,

So, with this query, I can now use this:
Date:

StartDate:  1/1/900 (or whatever date range I wish to use)      EndDate: 12/17/2009 or whatever date range I wish to use?

to get same result I would have gotten if I used this:


StartDate
   From:     1/1/2009                          To:   12/17/2009
EndDate
   From:   10/1/2000                          To: 12/17/2009




0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
One more change required..

SELECT * from mytable
where id = givenid
and startdate >= isnull(givenDate, '1/1/1900')
and enddate <= isnull(givenDate , getdate())

>> 12/17/2009 or whatever date range I wish to use?

I used getdate() to work with the latest date..
For today it would be 12/17 and tomorrow it would be 12/18

Hope this helps..
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Goodangel MatopeSoftware ArchitectCommented:
I would like to suggest another change. I have realised from experience that if your date values have a time component in them, for example, if your end date is 2009-10-10 12:45:59.0000 you can face some problems when you use a clause like "and enddate <= '2009-10-10' " because the time component will make your end date greater than '2009-10-10 00:00:00.0000'. I think it is much safer to use DATEDIFF when doing date comparisons to make sure that you eliminate the time component of the date as a factor in your query.

I am not sure if I am making sense to you, but I would recommend using the following:
We shall declare 4 vairables, @StartDateStart, @StartDateEnd, @EndDateStart, @EndDateEnd.
The query will search for all records in which the start date is between @StartDateStart and @StartDateEnd,
and where the EndDate is between @EndDateStart and @EndDateEnd

You can modify the query to your needs, but this is the basic template

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


DECLARE @StartDateStart SMALLDATETIME
DECLARE @StartDateEnd SMALLDATETIME
DECLARE @EndDateStart SMALLDATETIME
DECLARE @EndDateEnd SMALLDATETIME

SET @StartDateStart = '2001-01-01'
SET @StartDateEnd = '2001-12-31'
SET @EndDateStart = '2009-01-01'
SET @EndDateEnd = '2009-12-31'

SELECT *
FROM myTable
WHERE [ID] = givenID
AND DATEDIFF(day,@StartDateStart, StartDate) >= 0
AND DATEDIFF(day,StartDate,@StartDateEnd) >= 0
AND DATEDIFF(day,@EndDateStart, EndDate) >= 0
AND DATEDIFF(day,EndDate,@EndDateEnd) > = 0
0
 
aplusexpertCommented:
Try this way

(CAST(FLOOR(CAST([tblEmployee].[ResignDate] AS float)) AS datetime) >= ''' + CONVERT(varchar(20),@MonthStartDate) + '''
0
 
jeyan10Commented:
Hi,
 
 This Query would help

 SELECT * from mytable
where id = givenid
and startdate >= isnull(givenDate, '1/1/1900')
and enddate <= isnull(givenDate , getdate())
0
 
simflexAuthor Commented:
i want to thank all of you for your help on this.
At this time, each of the queries you provided has given me partial results.

For instance, if I take this code:

 SELECT * from mytable
where id = givenid
and startdate >= isnull(givenDate, '1/1/1900')
and enddate <= isnull(givenDate , getdate())

and I give it the following values:

 SELECT * from mytable
where id = givenid
and startdate >= isnull('10/01/2009' , '1/1/1900')
and enddate <= isnull('12/01/2009' , '07/01/2009' )

when I run this for instance, the startDate should falling within 1/1/1900 and the end date should fall within 07/01/2009 and 12/01/2009.

So far, the startdate is giving values that even beyond 12/01/2009 and should not.

0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Then it should be something like your first query:

 SELECT * from mytable
where id = givenid
and startdate between '1/1/1900' and '10/01/2009'
and enddate between '07/01/2009' and '12/01/2009'
0
 
simflexAuthor Commented:
Even though this isn't working yet, I want to thank you all again for your time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.