Using yesterdays date

Discussion in 'C#' started by dtfrancis15, Jun 23, 2009.

0
  1. #1
    THe script below is used to display the number of calls closed yesterday, but obviously the SQL statement being used is taking the past 24 hours.

    How can I amend this so it only takes yesterdays calls (obviously MySQL uses the time in the field as well) and also to let me use todays date to work out how many today.

    <%
    SQL="SELECT Count(CallReference) as CallCount FROM supportv3 WHERE Status = 'CL' and call_closer = 'Dave Francis' AND last_update > DATE_SUB(NOW(), INTERVAL 1 DAY)"

    set CallCount=conn.execute(SQL)

    If trim("" & CallCount(0)) = "" then

    CallCount3=0

    Else CallCount3 = CallCount(0)

    End IF
    %>

    <%=CallCount3%>
     
    dtfrancis15, Jun 23, 2009 IP
  2. dgxshiny

    dgxshiny Greenhorn

    Messages:
    65
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    23
    #2
    You will need to add an upper limit, try:

    WHERE Status = 'CL' and call_closer = 'Dave Francis' AND last_update > DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND last_update < CURDATE()
     
    dgxshiny, Jun 23, 2009 IP
  3. dopanel.com

    dopanel.com Peon

    Messages:
    93
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    datediff(time1,time2,"d")
     
    dopanel.com, Jun 24, 2009 IP
    dgxshiny likes this.
  4. DoDo Me

    DoDo Me Peon

    Messages:
    2,257
    Likes Received:
    27
    Best Answers:
    0
    Trophy Points:
    0
    #4
    if is yesterday, the practice is:

    dateadd(today(year-month-day) 00:00, -1)
     
    DoDo Me, Jun 24, 2009 IP