SQL Server Performance

Search orders between two dates (both included)

Discussion in 'T-SQL Performance Tuning for Developers' started by Cesar, Nov 30, 2005.

  1. Cesar New Member

    Hi,

    I want to search orders by date between two given dates by user (both included), and I don#%92 t know how to do it.

    In this way it doesn#%92 t work:

    WHERE (o.theDate >= @from_date and o.theDate < @to_date)


    Only are returned dates greater or equal than the first one (@from_date) and smaller than the second one (@to_date).

    How can I do it so that if user specify for example @from_date = 29/11/2005 and @to_date = 30/11/2005, both dates are included in returned records? And also, if user specify equal date for both ‘from#%92 and ‘to#%92, for example @from_date = 30/11/2005 and @to_date = 30/11/2005 orders with this date (30/11/2005) are returned?

    Thank you
  2. Adriaan New Member

    Your < operator means that @to_date is not included, so replace that with a <= operator. That, or use this:
    WHERE o.theDate BETWEEN @from_date AND @to_date.

    This is just the start, because if your o.theDate includes the time of day, other than 0:00:000, and you have entries for @to_date with a time after 0:00:000, then those entries will not be returned.

    In that case, use
    WHERE CONVERT(VARCHAR(10), o.theDate, 120) BETWEEN @from_date AND @start_date

    Frank Kalis has written an excellent article on how to handle dates, it's on this site somewhere.
  3. Cesar New Member

    Hi Adriaan,

    I already tried '<=' operator and 'BETWEEN' condition and it didn' t work. I suppose it' s because the reason you said:


    quote:because if your o.theDate includes the time of day, other than 0:00:000, and you have entries for @to_date with a time after 0:00:000, then those entries will not be returned.

    I am interested in CONVERT function you mentioned. What does exactly this?:
    CONVERT(VARCHAR(10), o.theDate, 120)
  4. Luis Martin Moderator

    120 means yyyy-mm-dd hh:mi<img src='/community/emoticons/emotion-7.gif' alt=':s' />s(24h) format.<br />So, Select Convert (varchar(10), o.thedate,120) will return i.e:<br />2005-01-01<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  5. Cesar New Member

    Thank you very much LuisMartin [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Muchas gracias, [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Cèsar
  6. Madhivanan Moderator

    http://www.sql-server-performance.com/fk_datetime.asp

    Madhivanan

    Failing to plan is Planning to fail
  7. srinivas913 New Member

    Hi,i m very beginar to sqlserver,I want to display data,Example i want to display data of 15,16 (documenttypes)
  8. FrankKalis Moderator

    [quote user="srinivas913"]Hi,i m very beginar to sqlserver,I want to display data,Example i want to display data of 15,16 (documenttypes)[/quote]
    Welcome to the forum!
    Could you please start a new thread for your question?
    From your question it seems like you are looking for something really basic like SELECT ... FROM table WHERE DocumentTypeKey IN (15, 16)

Share This Page