Professional Web Applications Themes

search for records between dates - Microsoft SQL / MS SQL Server

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 com Interested in ...

  1. #1

    Default search for records between dates

    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
    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
    com
    ______________________


    Davef Guest

  2. #2

    Default Re: search for records between dates

    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


    "Davef" <com> wrote in message
    news:e4Vse$phx.gbl...
    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
    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
    com
    ______________________



    Narayana Guest

  3. #3

    Default Re: search for records between dates

    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.


    "Davef" <com> wrote in message
    news:e4Vse$phx.gbl... 
    userName, 
    Contacts.CONTACT_ID 
    contact 


    Jacco Guest

  4. #4

    Default Re: search for records between dates

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

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... [/ref]
    bring [/ref]
    8:03:00 
    > userName, 
    > Contacts.CONTACT_ID [/ref]
    company.COMPANY_ID 
    > contact 
    >
    >[/ref]


    Davef Guest

  5. #5

    Default Re: search for records between dates

    > 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?


    Aaron Guest

  6. #6

    Default Re: search for records between dates

    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')



    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... 
    >
    > What does "did not work" mean? Could you show your table structure,[/ref]
    sample 


    Davef Guest

  7. #7

    Default Re: search for records between dates

    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 yzer:

    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.




    "Davef" <com> wrote in message
    news:phx.gbl... 
    userName, 
    Contacts.CONTACT_ID 
    > >
    > > What does "did not work" mean? Could you show your table structure,[/ref]
    > sample 
    >
    >[/ref]


    Aaron Guest

Similar Threads

  1. search across multiple records?
    By Geoff in forum MySQL
    Replies: 5
    Last Post: March 10th, 07:41 PM
  2. Search between dates
    By Angie2099 in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: July 10th, 03:22 AM
  3. Default Dates & Times in Existing Records
    By boconnor in forum Microsoft Access
    Replies: 1
    Last Post: August 1st, 01:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139