Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2006
    Posts
    24

    Unanswered: SQL Statement Help

    I have a database with a table named events and 2 date/time fields named display_date and display_time.

    I need to build a sql statement that will display the proper record on the right date and the right time and then move to the next record based on the date and time in those fields.

    i currently have:
    <%
    dte = Date()
    hrs = Now()

    strSQLQuery = "SELECT * FROM Events Where Display_date=Date() and Display_Time=Now()"

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3
    %>

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mwood
    ...and 2 date/time fields named display_date and display_time.
    any particular reason why? i mean, the SQL would be so much easier if you used just one

    not so good --
    display_date: 2007-10-22 00:00:00
    display_time: 1970-01-01 09:37:00

    a lot better --
    display_datetime: 2007-10-22 09:37:00

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    24
    yes .. because we may have 2 events on the same day and i will need to display the first one till maybe noon and them i need it to switch to the next one.

  4. #4
    Join Date
    Nov 2006
    Posts
    24
    but if there is a better way to handle this i would be glad to try it.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by mwood
    yes .. because we may have 2 events on the same day and i will need to display the first one till maybe noon and them i need it to switch to the next one.
    So compare the display_datetime with the current date time..?
    If you want it displayed until tomorrow (24-Oct-07) 1:36pm then, as r937 has pointed out, store the full datetime value i.e. 2007-10-24 13:36:00.000
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2006
    Posts
    24
    I am still having trouble and really need some help here please.

    Ok I made a new field named Display_DateTime and changed my query to:

    <%
    dte=Date
    strSQLQuery = "SELECT * FROM Events Where Display_DateTime=Now()"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open strSQLQuery, conn, 3, 3
    %>

    <% if rs("Display_DateTime")<>"" then %>
    <td background="images/events/event_bg.gif"><font face="BellGothic Blk BT" size="4" color="#FFFFFF"><%=rs("event_name" & prefixtable)%></font></td>
    <%else%>
    <td background="images/events/event_bg.gif"><font face="BellGothic Blk BT" size="4" color="#FFFFFF"><%response.write("There are no events scheduled")%></font></td>
    <%end if%>

    And it won't work. Also do a need a Do While and Loop statement?

  7. #7
    Join Date
    Nov 2006
    Posts
    24
    I need to make sure the current event remains displayed until the next Display_DateTime is right

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when you use Now(), it produces the current datetime, down to the millisecond at the time you execute it

    i think it would be a huge coincidence to find a row in the table which had been stored with that exact time ahead of time



    but i still don't understand, what does "remains displayed until the next Display_DateTime is right" mean?

    how are you storing values in that column? can you show the actual INSERT statement?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2006
    Posts
    24
    What i mean is that Event 1 remains displayed until event 2 comes up based on Display_DateTime

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how are you storing values in that column? can you show the actual INSERT statement?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2006
    Posts
    24
    I insert them manually .. I have the server this is hosted on.

    The Table is Events

    Fields Are:

    Event_Name (memo field)
    Event_Date (text field)
    Event_Time (text field)
    Event_Location (memo field)
    Event_Info (memo field)
    Display_DateTime (date/time field)

    This isn't about inserting the data it is about displaying the data.

  12. #12
    Join Date
    Nov 2006
    Posts
    24
    it is at larryricketts.com if it helps you see what i am trying to do.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let me come at this a different way

    your table contains past and future events, correct? i mean, in the context of the current datetime Now(), which i think you'll agree you'll never actually hit, so each event is either on one side or the other of it, yes?

    is it fair to say that you want just the next upcoming event?

    that's actually easier than it looks

    Code:
    select top 1
         , as
         , you
         , wish
      from events
     where Display_DateTime > Now()
    order 
        by Display_DateTime asc
    you can remove the separate date and time columns from your table too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2006
    Posts
    24
    Ok .. yes the database will have past and future events in it. Lets say that i have an event coming up on november 1st but i want to begin dispalying it today and then on november 2nd i want it to change to the next event.

    I think we agree on that part. But lets say i have an event on november 1st at 8:00 am and another one at 12:00 pm. I need it to change to that next event at 8:01 am.

    Thus I have the Display_DateTime field. The Display_DateTime field is never displayed it is only the criteria to base what is displayed and when.

    The Event_Name, Event_Date, Event_Time, Event_Location and Event_Info are the fields that will actually get displayed.

    If we happen to get to the end of the database and there are no more records and all events have been displayed it will say "There Are No Events Scheduled"

    So what i am looking for is a way to make this happen.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mwood
    So what i am looking for is a way to make this happen.
    ok, do me a favour and try my query

    kthxbye

    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
  •