# How can I retrive data between two dates and between two different column i.e Date From and DateTo.

### Question

• I want to select data between two dates. first date is in one column i.e. "DateFrom" and second date is in second column i.e. "DateTo".

So how can I do query for this?

Tuesday, August 07, 2012 5:12 PM

• Hello all,

I have solved my problem with this query

declare @fromDate date
set @fromDate = '2012-08-15'

declare @toDate date
set @toDate = '2012-08-16'

declare @fromTime time
set @fromTime ='12:33:46'

declare @toTime time
set @toTime='03:33:46'

select sum(bookedproduct.Quantity) Quantity
from BookingDetails INNER JOIN BookedProduct on BookedProduct.CustomerID = BookingDetails.CustomerID

where ProductID=121
and (DateFrom >= @fromDate or DateTo <= @toDate) and (DateFrom between @fromDate and @toDate)
and (TimeFrom >=@fromTime or TimeTo <=@fromTime)
and(TimeFrom between @fromTime and @fromTime)

again thank you for your help.

Saturday, August 18, 2012 10:49 AM

### All replies

• you have two date fields, both will have multiple values. So how are you going to get data, there must be some date value which needs to be checked. Say there should be a value for checkDate below which you need to check between the date ranges:

```select * from table1 where checkDate between FromDate and ToDate
//or
select * from table1 where checkDate > Convert(Date, FromDate) and checkDate < Convert(Date, ToDate)```

regards

joon

Tuesday, August 07, 2012 5:22 PM
• Can you please elaborate on your question? Do you want to pass 2 dates as a date range also?

http://wiki.lessthandot.com/index.php/Date_Range_WHERE_Clause_Simplification

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Tuesday, August 07, 2012 9:47 PM
• I have four tables i.e.

1: Product

2: Customer

3:BookingDetails

4:BookedProduct

In first table i.e. Product there are three fields i.e. 1. ProductID[PK],2.ProductName,3.Quantity

In second table i.e. Customer there are four fields i.e. 1.CustomerID[PK],2.FirstName,3.LastName,4.PhoneNumber

In third table i.e. BookingDetails there are six fields i.e  1. CustomerID[FK],2.DateFrom, 3.DateTo, 4.TotalDays, 5.TimeFrom, 6.TimeTo

In fourth table i.e. BookedProduct there are five fields i.e. 1.CustomerID[FK],2.SupplierID[FK],3.ProductID[FK],4.ProductName,5.Quantity

my question is when any customer want to book any product in particular date for ex. datefrom = 2012-08-07 and dateto=2012-08-09

at that time I want to check how much Quantity remaining for a particular product  in our stock in between these dates.

So I want to count total booked product quantity in between these two dates.

How I can do query for this?

Thank you,

ajay

Wednesday, August 08, 2012 9:54 AM
• How do you connect BookingDetails with BookedProduct table?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Wednesday, August 08, 2012 12:24 PM
• Hello, Naomi N

BookingDetails and BookedProduct have CustomerID as a Foriegn key.

Thank you,

ajay

Wednesday, August 08, 2012 6:35 PM
• They have it as a foreign key, but I don't see a common column  between two tables? Or are you saying that each customer can have only a single BookingDetails and BookedProduct per customer? E.g. customer will identify the common rows between these two tables?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Thursday, August 09, 2012 7:52 AM
• In BookingDetails , BookedProduct table I use CustomerID.
I can get details through this ID.

One customer can book more then one product at a time.

I have tried this query

```SELECT        sum(BookedProduct.Quantity)
FROM            BookedProduct INNER JOIN
Customer ON BookedProduct.CustomerID = Customer.CustomerID INNER JOIN
BookingDetails ON Customer.CustomerID = BookingDetails.CustomerID INNER JOIN
Product ON BookedProduct.ProductID = Product.ProductID
where BookedProduct.ProductID = 121 and  DateFrom='2012-08-01' and  DateTo='2012-08-09' ```

But it require CustomerID.

If I update above query like this

```SELECT        sum(BookedProduct.Quantity)
FROM            BookedProduct INNER JOIN
Customer ON BookedProduct.CustomerID = Customer.CustomerID INNER JOIN
BookingDetails ON Customer.CustomerID = BookingDetails.CustomerID INNER JOIN
Product ON BookedProduct.ProductID = Product.ProductID
where BookedProduct.ProductID = 121 and bookedproduct.CustomerID=111121```

It gives sum of product quantity of productID =121 where customerid =111121

I don't want like this.

I want to define following terms in query

DateFrom And DateTo is required.

Saturday, August 11, 2012 9:49 AM
• Hello all,

I have solved my problem with this query

declare @fromDate date
set @fromDate = '2012-08-15'

declare @toDate date
set @toDate = '2012-08-16'

declare @fromTime time
set @fromTime ='12:33:46'

declare @toTime time
set @toTime='03:33:46'

select sum(bookedproduct.Quantity) Quantity
from BookingDetails INNER JOIN BookedProduct on BookedProduct.CustomerID = BookingDetails.CustomerID

where ProductID=121
and (DateFrom >= @fromDate or DateTo <= @toDate) and (DateFrom between @fromDate and @toDate)
and (TimeFrom >=@fromTime or TimeTo <=@fromTime)
and(TimeFrom between @fromTime and @fromTime)

again thank you for your help.

Saturday, August 18, 2012 10:49 AM