search for records between dates

search for records between dates

Post by Dave » Tue, 19 Aug 2003 17:45:15



I am doing a search for record between certain dates... It will not bring
back the recond if the salesdate has a time in it like...8/14/2003 8:03:00
PM Unless I change the search date to 8/15/2003

SELECT     tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' +
Contacts.LAST_NAME AS theName, company.COMPANY,
                      Users.First_Name + ' ' + Users.Last_Name AS userName,
tempSales.SalesDate, tempSales.SalesDateThrough
FROM         tempSales INNER JOIN
                      Contacts ON tempSales.CONTACT_ID = Contacts.CONTACT_ID
INNER JOIN
                      company ON Contacts.COMPANY_ID = company.COMPANY_ID
INNER JOIN
                      Users ON tempSales.sentByID = Users.UserID
WHERE     (tempSales.SalesActive = 1) AND (tempSales.SalesDate BETWEEN
'12/01/2002' AND '8/14/2003')

--

______________________
David Fetrow
HelixPoint LLC.
http://www.helixpoint.com

Interested in Affordable Email Marketing?
Check out the HelixMailer at http://www.helixpoint.com/helixmailer.asp
If you are interested in becoming a Reseller of HelixPoint products, contact

______________________

 
 
 

search for records between dates

Post by Narayana Vyas Kondredd » Tue, 19 Aug 2003 18:04:42


You could specify a time along with the date in your WHERE clause. For
example:

AND (tempSales.SalesDate BETWEEN '12/01/2002'  AND '8/14/2003 23:59:00')

Also, the dateformat you are using could get you into problems. Stick to
YYYYMMDD format.

For more information on querying date data, see:
http://vyaskn.tripod.com/searching_date_time_values.htm

--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm


I am doing a search for record between certain dates... It will not bring
back the recond if the salesdate has a time in it like...8/14/2003 8:03:00
PM Unless I change the search date to 8/15/2003

SELECT     tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' +
Contacts.LAST_NAME AS theName, company.COMPANY,
                      Users.First_Name + ' ' + Users.Last_Name AS userName,
tempSales.SalesDate, tempSales.SalesDateThrough
FROM         tempSales INNER JOIN
                      Contacts ON tempSales.CONTACT_ID = Contacts.CONTACT_ID
INNER JOIN
                      company ON Contacts.COMPANY_ID = company.COMPANY_ID
INNER JOIN
                      Users ON tempSales.sentByID = Users.UserID
WHERE     (tempSales.SalesActive = 1) AND (tempSales.SalesDate BETWEEN
'12/01/2002' AND '8/14/2003')

--

______________________
David Fetrow
HelixPoint LLC.
http://www.helixpoint.com

Interested in Affordable Email Marketing?
Check out the HelixMailer at http://www.helixpoint.com/helixmailer.asp
If you are interested in becoming a Reseller of HelixPoint products, contact

______________________

 
 
 

search for records between dates

Post by Jacco Schalkwij » Tue, 19 Aug 2003 18:10:16


Hi Dave,

Datetime in SQL Server always includes the time, and if you don't provide
the time with the date the time will be assumed to be midnight. For that
reason it is a good practice if you are looking for dates within a certain
range not to use BETWEEN ... AND, but to use >= and <, in your example:

tempSales.SalesDate >= '12/01/2002' AND tempSales.SalesDate < '8/15/2003')

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


> I am doing a search for record between certain dates... It will not bring
> back the recond if the salesdate has a time in it like...8/14/2003 8:03:00
> PM Unless I change the search date to 8/15/2003

> SELECT     tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' +
> Contacts.LAST_NAME AS theName, company.COMPANY,
>                       Users.First_Name + ' ' + Users.Last_Name AS
userName,
> tempSales.SalesDate, tempSales.SalesDateThrough
> FROM         tempSales INNER JOIN
>                       Contacts ON tempSales.CONTACT_ID =
Contacts.CONTACT_ID
> INNER JOIN
>                       company ON Contacts.COMPANY_ID = company.COMPANY_ID
> INNER JOIN
>                       Users ON tempSales.sentByID = Users.UserID
> WHERE     (tempSales.SalesActive = 1) AND (tempSales.SalesDate BETWEEN
> '12/01/2002' AND '8/14/2003')

> --

> ______________________
> David Fetrow
> HelixPoint LLC.
> http://www.helixpoint.com

> Interested in Affordable Email Marketing?
> Check out the HelixMailer at http://www.helixpoint.com/helixmailer.asp
> If you are interested in becoming a Reseller of HelixPoint products,
contact

> ______________________

 
 
 

search for records between dates

Post by Dave » Tue, 19 Aug 2003 18:32:40


I did do that first, but that did not work.


> Hi Dave,

> Datetime in SQL Server always includes the time, and if you don't provide
> the time with the date the time will be assumed to be midnight. For that
> reason it is a good practice if you are looking for dates within a certain
> range not to use BETWEEN ... AND, but to use >= and <, in your example:

> tempSales.SalesDate >= '12/01/2002' AND tempSales.SalesDate < '8/15/2003')

> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.



> > I am doing a search for record between certain dates... It will not
bring
> > back the recond if the salesdate has a time in it like...8/14/2003
8:03:00
> > PM Unless I change the search date to 8/15/2003

> > SELECT     tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' +
> > Contacts.LAST_NAME AS theName, company.COMPANY,
> >                       Users.First_Name + ' ' + Users.Last_Name AS
> userName,
> > tempSales.SalesDate, tempSales.SalesDateThrough
> > FROM         tempSales INNER JOIN
> >                       Contacts ON tempSales.CONTACT_ID =
> Contacts.CONTACT_ID
> > INNER JOIN
> >                       company ON Contacts.COMPANY_ID =
company.COMPANY_ID
> > INNER JOIN
> >                       Users ON tempSales.sentByID = Users.UserID
> > WHERE     (tempSales.SalesActive = 1) AND (tempSales.SalesDate BETWEEN
> > '12/01/2002' AND '8/14/2003')

> > --

> > ______________________
> > David Fetrow
> > HelixPoint LLC.
> > http://www.helixpoint.com

> > Interested in Affordable Email Marketing?
> > Check out the HelixMailer at http://www.helixpoint.com/helixmailer.asp
> > If you are interested in becoming a Reseller of HelixPoint products,
> contact

> > ______________________

 
 
 

search for records between dates

Post by Aaron Bertrand - MV » Tue, 19 Aug 2003 18:39:14


Quote:> I did do that first, but that did not work.

What does "did not work" mean?  Could you show your table structure, sample
data, and desired results?
 
 
 

search for records between dates

Post by Dave » Tue, 19 Aug 2003 19:24:22


salesdate is a smalldatetime the specific record has this in the
field-----8/14/2003 8:03:00 PM

The following does not bring back the record

SELECT     tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' +
Contacts.LAST_NAME AS theName, company.COMPANY,
                      Users.First_Name + ' ' + Users.Last_Name AS userName,
tempSales.SalesDate, tempSales.SalesDateThrough
FROM         tempSales INNER JOIN
                      Contacts ON tempSales.CONTACT_ID = Contacts.CONTACT_ID
INNER JOIN
                      company ON Contacts.COMPANY_ID = company.COMPANY_ID
INNER JOIN
                      Users ON tempSales.sentByID = Users.UserID
WHERE     (tempSales.SalesActive = 1) AND (CONVERT(varchar,
tempSales.SalesDate, 101) BETWEEN '12/01/2002' AND '8/14/2003')



Quote:> > I did do that first, but that did not work.

> What does "did not work" mean?  Could you show your table structure,
sample
> data, and desired results?

 
 
 

search for records between dates

Post by Aaron Bertrand - MV » Tue, 19 Aug 2003 19:36:47


And what does this yield:

SELECT * FROM tempSales WHERE salesdate >= '20021201' and salesdate <
'20030815'

???

If this gives back rows, then it is either your join that is causing
exclusion, or your insistence on using character matching instead of dates
... as well as an ambiguous date format that doesn't sort well.  Try running
this in Query Analyzer:

SELECT CASE WHEN '8/14/2002' > '12/1/2003' THEN 'weird?' ELSE 'expected' END

Obviously, 8/14/2002 is NOT greater than 12/1/2003.  But in character
comparisons, each character is inspected individually (not as an entire date
value, which you seem to expect)... and indeed, '8' > '1'.

So, use DATETIME values, not VARCHAR values, to compare DATETIME values.
And use a reliable date format, like YYYYMMDD.  Using the WHERE clause I
have above, instead of your convert and between, should yield better results
(again, assuming your JOIN portion isn't a culprit).  I can't verify that
because you still haven't shown us table structure, sample data, and desired
results.


> salesdate is a smalldatetime the specific record has this in the
> field-----8/14/2003 8:03:00 PM

> The following does not bring back the record

> SELECT     tempSales.tempSalesID, Contacts.FIRST_NAME + ' ' +
> Contacts.LAST_NAME AS theName, company.COMPANY,
>                       Users.First_Name + ' ' + Users.Last_Name AS
userName,
> tempSales.SalesDate, tempSales.SalesDateThrough
> FROM         tempSales INNER JOIN
>                       Contacts ON tempSales.CONTACT_ID =
Contacts.CONTACT_ID
> INNER JOIN
>                       company ON Contacts.COMPANY_ID = company.COMPANY_ID
> INNER JOIN
>                       Users ON tempSales.sentByID = Users.UserID
> WHERE     (tempSales.SalesActive = 1) AND (CONVERT(varchar,
> tempSales.SalesDate, 101) BETWEEN '12/01/2002' AND '8/14/2003')



> > > I did do that first, but that did not work.

> > What does "did not work" mean?  Could you show your table structure,
> sample
> > data, and desired results?

 
 
 

1. searching the records on date of range

Dear all,
Sir, i want to search a records based on the range of dates
but it gives an following error

Run-time error: -2147217913(86040e07)
[Microsoft][ODBC Microsoft Driver] Data type mismatch in
criteria
expression.

for searching i have written the code as follows

rsDate.Open "select * from CustomerPerInfo
where DateOfVisit between '" & dtFrom & "' and '" & dtTo
& "'"

in the above query i have taken the variables
dtFrom ant dtTo as Date datatype.

the format of the dtFrom and dtTo variable is general
and the DateOfVisit field in the table is also in general
format

i have tried by taking the CDate function but it didn't
work
i also tried by taking the variables as variant and then
convert it
into the date but it also didn't work.

so please suggest me the soln for the same.

2. Creating Login name for same user in two different domains?

3. Search record with recent Date

4. Failed when using TCP/IP Net Library

5. record searching in database using range of date

6. How to Set field for decimal number???

7. Search record with recent Date

8. Database copy

9. searching records in database on range of dates

10. How can I Search for a record in an Access Database that contains my search query

11. Searching a date range with a date range ??

12. Searching and recording data from within a record

13. Filemaker V : find records from this date till that date