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

    Join Date
    Nov 2005
    Posts
    128
    Rep Power
    0

    Date greater than


    Hi Guys

    I have a table with id, date column in it. How do i list all records which has date greater than 30 day?

    Thanks


    Is this Correct?

    SELECT * FROM TABLE WHERE DATEDIFF(DAY, DATE, GETDATE()) > 30
    Last edited by good_luck; July 24th, 2007 at 09:33 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    339
    Rep Power
    48
    yup.

    Comments on this post

    • f'lar agrees
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    greater than 30 days ago, or greater than 30 days away?

    (it makes a difference if the table contains, say, payments due in the future)

    if greater than 30 days away, you want

    ... WHERE DATEDIFF(DAY, GETDATE(), DATE) > 30

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Posts
    128
    Rep Power
    0
    Thanks Guys it works great......

    Got Another problem
    I have table with say data like this
    serial id date1 date2 col1 col2 col3
    ------------------------------------------------------------
    1 id_1 2007/20/05 null abc yes no
    2 id_1 2007/20/05 null abc yes no
    3 id_1 2007/20/05 2007/20/05 abc1 yes no
    4 id_2 2007/20/05 2007/20/05 abc yes no
    5 id_3 2007/20/05 2007/20/05 abc yes no
    6 id_4 2007/20/05 null abc yes no
    7 id_4 2007/20/05 2007/20/05 abc yes no
    8 id_5 2007/20/05 null abc yes no
    9 id_6 2007/20/05 null abc yes no
    10 id_6 2007/20/05 2007/20/05 abc yes no



    Need output like this one


    4 id_2 2007/20/05 2007/20/05 abc yes no
    5 id_3 2007/20/05 2007/20/05 abc yes no



    Pick only those record that that has date1, col1,col2,col3 match for each id(column). id_1 match the condition but has more records which i don't want to include these id in my list

    Any help

    Thanks
    Last edited by good_luck; July 25th, 2007 at 08:39 PM.
  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 good_luck
    Pick only those record that that has date1, col1,col2,col3 match for each id(column). id_1 match the condition but has more records which i don't want to include these id in my list
    ????????

    and why is id-5 excluded?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Posts
    128
    Rep Power
    0
    Originally Posted by r937
    ????????

    and why is id-5 excluded?


    I am looking for records which has conditions as
    date1 is not null
    col1 as abc
    col2 as yes
    col3 as no
    date2 is null

    and important
    if there is another record for the same id with date2 as not null then don't pick any record for that id. I want those records which has only one records with date2 as not null for each group of id's

    Thanks
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    your last post does not describe id_2 and id_3, which your previous post says should be returned
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Posts
    128
    Rep Power
    0
    Originally Posted by r937
    your last post does not describe id_2 and id_3, which your previous post says should be returned
    That's correct i made mistake in describing it.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2005
    Posts
    128
    Rep Power
    0
    Originally Posted by good_luck
    That's correct i made mistake in describing it.
    Is it possible or not and if it yes can anybody show push me in that direction.

    Thanks
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    it's probably possible, but i have no idea what you're asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo