#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10

    Date query doesn't work


    Hi,

    can anyone see what's wrong with this query? It is suppose to select dates from the last 8 years ( i will adjust it to days when i've made it work):


    SELECT * FROM ac where LckSt='0' and (CONVERT(VARCHAR(10), ChDt, 111) between CONVERT(VARCHAR(8), GETDATE(), 111) and CONVERT(VARCHAR(8), dateadd(year,-8,getdate()), 111))

    the format of ChDt is 20060503 (year month day).

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    reverse the two values in the BETWEEN so that the earlier one comes first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10
    Thanks,

    that helped a little!

    There are however still some dates that are still not displayed that are within the between dates, even though those dates actually should be displayed. i'm not sure why this happends.

    When I try to do the following query then the displayDate are still shown in the format 20110324. Dont know if this has something to do with it?

    SELECT CONVERT(VARCHAR(10), ChDt, 111) as displayDate,* FROM R7 where LckSt='0' and CONVERT(VARCHAR(10), ChDt, 111) between CONVERT(VARCHAR(10), dateadd(year,-8,getdate()), 111) and CONVERT(VARCHAR(10), GETDATE(), 111)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10
    never mind, made it work when I changed from 111 to 112 in the convert function.

    thanks for the help
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Dag
    never mind, made it work when I changed from 111 to 112 in the convert function.
    you should not be using any conversions in the WHERE clause, just in the SELECT clause for output formatting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2004
    Posts
    136
    Rep Power
    10
    just out of curiousity, can I ask why? I allready added the sql query to some different scripts, rather not want to change it if it works
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by Dag
    just out of curiousity, can I ask why?
    it is not necessary to do the conversion (assuming your column is a DATE or DATETIME), because date comparison is more efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo