Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Angry Unanswered: BETWEEN syntax at SQL

    Hi,

    I would like to know how MSSQL handles dates for BETWEEN syntax at SQL statements.

    e.g. SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

    I find that the result rows do not include ones with [date]='2003/12/31'

    So, now I'm using BETWEEN '2003/01/01' AND '2004/01/01' instead.

    Is it a standard way to perform this kind of task?

  2. #2
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    between ... and ... is equal >= and <=
    So I think u can not get the data that date column value is bigger than 2003/12/31 00:00:00

    SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND '2003/12/31'

    must change to ==>

    SELECT * FROM [relation] WHERE [date] BETWEEN '2003/01/01' AND dateadd(day,1,'2003/12/31')

  3. #3
    Join Date
    Jan 2004
    Location
    Atlanta, GA
    Posts
    4
    enhydraboy is right with the date time of '2003/12/31 00:00:00'
    This is where your between will have a problem when comparing
    '2003/12/31' against lets say '2003/12/31 12:45:30'

    some times I just force it to '2003/12/31 23:59:59'
    when I use date and time : )

    Hope this helps

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by TrueCodePoet
    enhydraboy is right with the date time of '2003/12/31 00:00:00'
    This is where your between will have a problem when comparing
    '2003/12/31' against lets say '2003/12/31 12:45:30'

    some times I just force it to '2003/12/31 23:59:59'
    when I use date and time : )

    Hope this helps
    Well ...
    The standard i would use is

    SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Nov 2003
    Posts
    94
    Originally posted by Enigma
    Well ...
    The standard i would use is

    SELECT * FROM [sysobjects] WHERE convert(datetime,convert(varchar(10),[crdate],121)) BETWEEN '2003/01/01' AND '2003/12/31'
    That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

    Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

    And if he wants to include all of the last day of the month, it's

    BETWEEN '1 Jan 2004' AND '1 Feb 2004'

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by HanafiH
    That is, quite frankly, bonkers. You are forcing a column conversion on every row in the table.

    Would it not make a more sargeable filter to convert the between varchars to the date format required? It happens once in the excution of the query and applies to all rows with no further conversion.

    And if he wants to include all of the last day of the month, it's

    BETWEEN '1 Jan 2004' AND '1 Feb 2004'
    Never did I think of it that way ... and believe me .. I have been using it like this for past two years ... thanks for correcting me ... you have been a great help

    Well .. you learn at least one new thing on this site everyday
    Get yourself a copy of the The Holy Book

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

  7. #7
    Join Date
    Jan 2004
    Posts
    49
    Writing International Transact-SQL Statements
    A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators). Using the ISO format is more "international," and is independent of the default language. For more information, see the CONVERT function in the SQL Server Books Online.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It makes no difference what format the datetime value is sent in. It does make a difference whether the datetime value includes a time portion or not. Many applications cannot restrict themselves to whole date values, and thus some sort of conversion or adjustment is necessary to include all events occuring on the last day.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by buser
    A possible solution to this is to use the ISO Standard format for sending the datetime data to SQL Server, which is "YYYYMMDD" (no separators).
    excellent advice

    one minor point: ISO standard does include separators

    see Numeric representation of Dates and Time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Location
    Atlanta, GA
    Posts
    4
    Sorry.. I might have been confusing .. I will just post what I usualy do.
    Code:
    SELECT * FROM [sysobjects] WHERE [crdate]
     BETWEEN 
    convert(datetime,'2003/01/01 00:00:00',120)
     AND 
    convert(datetime,'2003/12/31 23:59:59',120)
    the two functions should only be run once durring the compile
    : )

    I have not noticed much difference with this query on 1 mill plus records
    Only reasonI don't use the 121 format is the Milleseconds is not needed for me Most of the Time I use the 101 format.

    hope this helps

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT * FROM [sysobjects] WHERE [crdate]
     BETWEEN 
    convert(datetime,'2003/01/01 00:00:00',120)
     AND 
    convert(datetime,'2003/12/31 23:59:59',120)
    actually you don't have to convert them

    when you put the year first like that, the database will assume that the next field is the month, etc.

    SELECT * FROM [sysobjects]
    WHERE [crdate]
    BETWEEN '2003/01/01 00:00:00'
    AND '2003/12/31 23:59:59'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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