Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: problem with displaying dates

    id start end ystart mstart dstart yend mend dend eventinfo
    1 12/31/08 12/31/08 2008 12 31 2008 12 31 EVENT1
    2 12/01/08 01/03/09 2008 12 1 2009 1 3 EVENT2
    3 12/01/08 02/11/09 2008 12 1 2009 2 11 EVENT3

    Hi everybody I have this table of appointments with three records the main things here are start and end (date start and date end) the dstart, mstart, yend are datepart derived from the field start and yend, mend , dend are datepart derived from end that has became int datatype already after datepart...

    what i need to do is to display events per month with the condition that if the event last more than one month or more than the current year it will also display on the month and year of it's entire duration..

    my query is this

    SELECT *
    FROM CalEvents
    WHERE mstart=12 AND ystart>=2008 AND mEnd>=1 and yend>=2008

    with the query the result would display all three records but if i change the mstart to 1 it should display records 2 and 3 and if i change mstart to 2 it would display only the third records..

    am confused of wht to do..
    thanks
    alex

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you should do is pretend that the columns ystart, mstart, dstart, yend, mend, dend are not in the table

    how would you do what you want to do using only start and end dates?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Posts
    87
    Quote Originally Posted by r937
    what you should do is pretend that the columns ystart, mstart, dstart, yend, mend, dend are not in the table

    how would you do what you want to do using only start and end dates?

    maybe i could use select * from calevents where startdate= ___ and endate =____

    is this the one you're referring?

  4. #4
    Join Date
    Nov 2008
    Posts
    23
    Hi Alex,
    If I am not wrong, are you referring to "mend" in this statement?

    "but if i change the mstart to 1 it should display records 2 and 3 and if i change mstart to 2"

    If that's the case, here is what in my opinion should work for your situation.

    declare @mend int = 3
    select *
    from jk_temp
    where
    mstart = 12
    and ystart >= 2008
    and mend >= @mend
    and yend >= 2008

    If this is not what you are looking for, then I am missing something very badly. I would be glad to help you out, if you are still working on this. If you have resolved yourself. Kudos. have a nice day.
    jambu

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alexiop
    maybe i could use select * from calevents where startdate= ___ and endate =____

    is this the one you're referring?
    yes, that's right, although obviously yout wouldn't use an equality test if you're looking for events for a particular month
    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
  •