Convert string to date datatype in SQL Server in PHP Laravel

I’m trying to get a product by id between a specific date range but the problem I’m facing is:

1. date is in string format
2. for some weird reason dates are stored in the database with an incomplete timestamp (eg: ’12-04-2020 12:21′) which I’m trying to get rid of

Using Laravel’s Eloquent below query works without the last line (AND…) which is my attempt to perform date conversion.

I may be able to solve this issue with MySQL but current task is to use SQL Server not really sure what went wrong here any help is much appreciable.

$id = 2;
$from = '01-03-2020';
$to = '01-05-2020';



            $products = DB::select("SELECT DISTINCT
                                products.PRODUCT_ID,
                                products.PRODUCT_NAME,
                                TRANSACTIONS_DTL.PRODUCT_ID,
                                TRANSACTIONS_DTL.TRANS_ID,
                                TRANSACTIONS_DTL.PRODUCT_VALUE,
                                TRANSACTIONS_HD.TRANS_ID,
                                TRANSACTIONS_HD.TRANS_DATE
                             FROM
                             products
                                INNER JOIN TRANSACTIONS_DTL ON products.PRODUCT_ID = TRANSACTIONS_DTL.PRODUCT_ID
                                INNER JOIN TRANSACTIONS_HD ON TRANSACTIONS_DTL.TRANS_ID = TRANSACTIONS_HD.TRANS_ID
                             WHERE
                                products.PRODUCT_ID = $id 
                            AND TRANSACTIONS_HD.TRANS_DATE > CONVERT(date,'$from') AND TRANSACTIONS_HD.TRANS_DATE < CONVERT(date,'$to')
        ");

Answer

It seems that you are storing datetime values as text in your table (if it’s possible, try to fix that issue). So, to find a solution to your problem, you may try the following:

• Convert the values in the column TRANSACTIONS_HD.TRANS_DATE using an appropriate style.
• Use an unambiguous format (yyyymmdd) for dates in $from and $to variables.
• Use parameter bindings in your SQL statements to provide protection against SQL injection.

Example (based on your code):

...

$id = 2;
$from = '20200301';
$to = '20200501';

$products = DB::select("
    SELECT DISTINCT
        products.PRODUCT_ID,
        products.PRODUCT_NAME,
        TRANSACTIONS_DTL.PRODUCT_ID,
        TRANSACTIONS_DTL.TRANS_ID,
        TRANSACTIONS_DTL.PRODUCT_VALUE,
        TRANSACTIONS_HD.TRANS_ID,
        TRANSACTIONS_HD.TRANS_DATE
    FROM products
    INNER JOIN TRANSACTIONS_DTL ON products.PRODUCT_ID = TRANSACTIONS_DTL.PRODUCT_ID
    INNER JOIN TRANSACTIONS_HD ON TRANSACTIONS_DTL.TRANS_ID = TRANSACTIONS_HD.TRANS_ID
    WHERE 
        (products.PRODUCT_ID = ?) AND 
        (CONVERT(date, TRANSACTIONS_HD.TRANS_DATE, 105) > CONVERT(date, ?)) AND 
        (CONVERT(date, TRANSACTIONS_HD.TRANS_DATE, 105) < CONVERT(date, ?))
", [$id, $from, $to]);

...