SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting data between two dates

    I have a sql database with date in the format
    2006-03-10 00:00:00.000

    I have the following select statement

    select * from database where date >= 1/5/2008 and date <= 7/3/2011

    which is in the format dd/mm/yyyy

    I am however getting dates in 2006 in my result.

    How do i convert the date in the database to same format?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    put quotes around your dates in the sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry yes i did have quotes but it is still giving a wrong result

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you show the revised query please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select * from database where date >= '1/5/2008' and date <= '7/3/2011'

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    microsoft sql server should have no problems with those date strings

    try it like this --

    ... where date >= '2008-01-05' and date <= '2011-07-03'

    if this still returns results in 2006 then something else is wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tried that, but still the same result. Not sure whats the problem

    Thanks

  8. #8
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I presume there's no issue in SQL Server in using date as a field name?
    Ian Anderson
    www.siteguru.co.uk

  9. #9
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No not that i have seen, I am using sql server 2005

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what is the datatype of this date column?

    also, why is this an asp question? have you tested the query outside of asp?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its a datetime

    I put it in asp because the program i am writing is in asp, but i guess it could go in a sql forum

    Thanks

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    okay, if it's datetime, have you tested the query outside of asp?

    because all versions of the query we've seen in this thread should definitely work correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tested it in the database itself and got the same result

    Thanks

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in that case, you should contact microsoft, they will be interested in learning of this bug in their database engine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •