?
Solved

how do I simply date range query?

Posted on 2009-12-17
9
Medium Priority
?
286 Views
Last Modified: 2012-05-08
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
 
0
Comment
Question by:simflex
9 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 80 total points
ID: 26077585
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
 

Author Comment

by:simflex
ID: 26077707
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26077937
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 11

Assisted Solution

by:Goodangel Matope
Goodangel Matope earned 80 total points
ID: 26079771
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
 
LVL 7

Assisted Solution

by:aplusexpert
aplusexpert earned 80 total points
ID: 26086064
Try this way

(CAST(FLOOR(CAST([tblEmployee].[ResignDate] AS float)) AS datetime) >= ''' + CONVERT(varchar(20),@MonthStartDate) + '''
0
 
LVL 1

Assisted Solution

by:jeyan10
jeyan10 earned 80 total points
ID: 26086303
Hi,
 
 This Query would help

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

Author Comment

by:simflex
ID: 26097338
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 26097858
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
 

Author Comment

by:simflex
ID: 26276239
Even though this isn't working yet, I want to thank you all again for your time.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question