Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2016
    Posts
    2

    Unanswered: ssql stmt - incorrect date comparison issue

    Hi

    In my database, I have a few fields that are strings and I am trying to write a query
    that converts the string to a date based on a From and To date selected by user and only display records that were created within that date range
    (as well as other options).

    It was working before I added in the last 2 lines with the dates in it.


    Example Code:
    $sql = "SELECT p.hdr_ID , u.ID, u.display_name, p.hdr_acord_date, p.hdr_company_addr, p.hdr_app_status ";
    $sql .= "FROM wp_users u ";
    $sql .= "INNER JOIN wp_usermeta m ON m.user_id = u.ID ";
    $sql .= "INNER JOIN wp_policies p ON p.hdr_sales_num = u.ID ";
    $sql .= "WHERE m.meta_key = 'wp_capabilities' AND m.meta_value LIKE '%salesperson%' ";
    $sql .= "AND p.hdr_sales_num = $sSalesID ";
    $sql .= "AND p.hdr_app_status = '$sAppStatus' ";

    $sql .= "AND STR_TO_DATE($sAccordFromDate, '%m/%d/%Y') >= STR_TO_DATE(p.hdr_acord_date, '%m/%d/%Y') ";
    $sql .= "AND STR_TO_DATE($sAccordToDate, '%m/%d/%Y') <= STR_TO_DATE(p.hdr_acord_date, '%m/%d/%Y') ";


    Any help is much appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    530
    Code:
    AND STR_TO_DATE(p.hdr_acord_date, '%m/%d/%Y') between STR_TO_DATE($sAccordFromDate, '%m/%d/%Y')  AND  STR_TO_DATE($sAccordToDate, '%m/%d/%Y')
    ... what is the result of a

    Code:
    select p.hdr_acord_date from wp_policies
    F. Celaia
    DBA Oracle/MS-SQL/MySQLSybase/DB2/

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •