Results 1 to 6 of 6

Thread: search by date

  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: search by date

    when i use enterprise manager, I type the following code to retrieve the date, can you check ?


    SELECT *
    FROM Table
    WHERE (ExpirationDate BETWEEN 11 / 1 / 2004 AND 2 / 1 / 2005)

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The slash symbol also represents the divisor, so the server is trying to perform 11 divided by 1 divided by 2004.

    Specify your dates as character strings, which SQL Server can implicitly conver to datetime values:

    BETWEEN '11/1/2004' AND '2/1/2005'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    I got it, thanks.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Which is inclusive, but would exclude all but mignight on 2/1

    these are datetime datatypes

    You need '2/1/2005 11:59:59'

    Or use DATEDIFF
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    In sql server you can use a string like 'yyyymmdd' (ex '20030611' for 11 june 2003) and it will be interpreted the same no matter the locale settings. I've had troubles in the past by getting different interpratations of a date '6/11/2003' on different clients. Locale settings US result in 11 june 2003, but locale settings UK result in 6 november 2003.

    You may also add a time part, but to select all timestamps until a certain date (ex '20030630'), you should select using the next day like

    <Date Field> < '20030701'

    The BETWEEN operator isn't suitable here, since it includes the upper bound.
    Last edited by DoktorBlue; 01-06-05 at 18:39.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, that's correct, so using DATEVAR >= '01/01/2004' and DATEVAR < '01/01/2005' will guarantee the full year's range, and will take advantage of an index if one exists.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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