Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 14th, 2004, 09:49 AM
Registered User
 
Join Date: May 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to spinto
Default A date interval with a date interval query

I'm trying to figure out a query that will return records if any dates between 2 user entered dates fall with a date range stored in the db. Lets say the 2 user dates are called UDate1 and UDate2 and the db date range is called DDate1 and DDate2. Any date between UDate1 and UDate2 must match any date between DDate1 and DDate2. This all needs to be done with a query.

 
Old May 14th, 2004, 10:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

I think you are wanted to retrieve records that are falls in a range of DATEs that's given.

EG:
SELECT RECORDS FROM TABLE
WHERE DATEFIELD BETWEEN StartDate and EndDate

Something like that?

For this it is enough if you have only one DATEFIELD in your database, if I am right.

This is how you got to go about.

Code:
Select * from <TABLENAME>
Where <DATEFIELD> between convert(datetime,'<DATE1>') and convert(datetime,'<DATE2>')
OR

Code:
Select * from <TABLENAME>
Where <DATEFIELD> >= convert(datetime,'<DATE1>') and 
<DATEFIELD> <= convert(datetime,'<DATE2>')
Replace <TABLENAME> with your table name, same way do that for <DATEFIELD>, <DATE1> and <DATE2> with relevant data.

Cheers!


-Vijay G
 
Old May 14th, 2004, 10:41 AM
Registered User
 
Join Date: May 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to spinto
Default

Actually there are two date fields in the database so it needs to search with a date range in a database. Any date within one date range must match any date within another date range.

 
Old May 14th, 2004, 10:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

As it is named this way, DDate1 & DDate2, I can't really understand what it means? That was why the doubt arised.

Quote:
quote:Any date between UDate1 and UDate2 must match any date between DDate1 and DDate2.
What is the connection between these two date Fields, as you say you wanted to get records between these dates.

[quote]Any date within one date range must match any date within another date range.[quote]

I am not sure what you are trying to explain there? Can you rephrase that so as to understand it better and help you?


-Vijay G
 
Old May 14th, 2004, 11:14 AM
Registered User
 
Join Date: May 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to spinto
Default

Sorry for not being clear. A user must enter a date range lets say 2/1/2004 to 2/20/2004. When they hit submit it must do a query against a date range in the database. Lets say there are two records in the DB

Date1 | Date2
1/25/2004 | 2/5/2004
1/4/2004 | 3/4/2004

Both these records will return records because dates within the date range in the database overlap with the dates entered by the user. Specifically for the first record the dates 2/2,2/3,2/4,2/5 fall with the date range specified by the user. In the second record the dates 2/1-2/20 fall within the user entered dates.

Select * from <TABLENAME>
Where <Any Date between DATEFIELD1 and DATEFIELD2> = <Any Date between USERENTEREDDATE1 and USERENTEREDDATE2>
 
Old May 14th, 2004, 11:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Ahhh... Now that's clear about what you are looking for.

As you say user enters date USERENTEREDDATE1 and USERENTEREDDATE2 should be converted to datetime datatype, if required.

Use
Code:
Convert(datetime, USERENTEREDDATE1)
Code:
Convert(datetime, USERENTEREDDATE2)
in the following query where ever required, if that doesn't produce the expected result. Also see to that the USERENTEREDDATE1 and USERENTEREDDATE2 are in the same format as that of the values in the database.

Code:
Select * from <TABLENAME>
Where 
(DATEFIELD1 <= USERENTEREDDATE1 and DATEFIELD1 < USERENTEREDDATE2)
and 
(DATEFIELD2 >= USERENTEREDDATE1 and DATEFIELD2 >= USERENTEREDDATE2)
I haven't tried this out in my PC here. But you can give a try.

Code:
Select * from <TABLENAME>
Where 
(USERENTEREDDATE1 between DATEFIELD1 and DATEFIELD2) 
and 
(USERENTEREDDATE2 between DATEFIELD1 and DATEFIELD2)
Hope that helps.
Cheers!

-Vijay G
 
Old May 14th, 2004, 12:20 PM
Registered User
 
Join Date: May 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to spinto
Default

That's not going to work. What if a user enters 2/1 - 2/27 and there are dates in the database 1/2 - 2/25. The record should come up because the database dates 2/1 - 2/25 overlap with the user entered dates 2/1 - 2/27. I might have come up with a solution though. Let me know what you think:

SELECT * FROM <TABLENAME>
WHERE
(USERENTERDATE1 <= DATEFIELD1 AND USERENTERDATE2 >= DATEFIELD2) OR
(USERENTERDATE1 >= DATEFIELD1 AND USERENTERDATE1 <= DATEFIELD2)

 
Old May 15th, 2004, 04:26 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Ok, I didnot think about that overlapping. That should be perfect.

Cheers!

-Vijay G





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help to query for data by 30-mins interval sunny76 Access 2 June 19th, 2008 11:52 AM
how to run code snippet in regular interval Swati Chari Beginning VB 6 2 March 12th, 2008 03:07 PM
Excel ADO 2.7 problem subindex out of interval xoc Beginning VB 6 1 April 15th, 2007 03:35 PM
tooltip interval??? nerssi HTML Code Clinic 5 August 4th, 2006 12:05 AM
How to select a time that is within a interval? kuzze MySQL 2 August 23rd, 2004 06:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.