Convert string to date datatype in SQL Server in PHP Laravel

Question

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

date is in string format
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')
        ");

What is the actual data type of the TRANSACTIONS_HD.TRANS_DATE column? — Zhorov
– Zhorov, Commented May 6, 2020 at 7:19

Greg Low · Accepted Answer · 2020-05-06 05:22:41Z

1

OK, on re-reading this, it looks like you'll need to do this instead:

AND CAST(SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 7, 4)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 4, 2)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 1, 2) 
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 12, 5)
        + ':00' AS datetime2) > '$from'
AND CAST(SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 7, 4)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 4, 2)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 1, 2) 
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 12, 5)
        + ':00' AS datetime2) < '$to'

Wish that was simpler. (Using an alias for the table would make it less dense)

In SQL Server, note that your $from and $to should just be strings in the format 'YYYYMMDD' e.g. '20201231'. That'll work regardless of which date data type is used, and regardless of language and regional settings. You just specify them as a string, you don't need to cast them.

NOTE: I've also assumed that the date format in your table is DD-MM-YYYY because I can't tell which way around they are from your examples. If your local date format is MM-DD-YYYY (i.e. US dates), then use this code instead:

AND CAST(SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 7, 4)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 1, 2)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 4, 2) 
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 12, 5)
        + ':00' AS datetime2) > '$from'
AND CAST(SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 7, 4)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 1, 2)
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 4, 2) 
        + SUBSTRING(TRANSACTIONS_HD.TRANS_DATE), 12, 5)
        + ':00' AS datetime2) < '$to'

edited May 6, 2020 at 5:22

answered May 6, 2020 at 1:13

Greg Low

1,5961 gold badge7 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2256465 Over a year ago

i tried the following but didnt work: AND CAST(TRANSACTIONS_HD.TRANS_DATE + ':00' AS date) > CAST('$from' + ':00' AS date)

user2256465 Over a year ago

i only tried with 'greater than' date only to be simple for now if it works ill include 'less than'

Greg Low Over a year ago

You don't need to add the ':00' to the $from and $to as they're already date values, and they also don't need to be cast. Also, in SQL Server, you should send dates as 'YYYYMMDD' and avoid any other format. (Note no dashes or slashes). That's the only safe date format that works for all date data types, and for all regional and language settings. I'm guessing what you need is more like:

Zhorov · Accepted Answer · 2020-05-06 07:44:34Z

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]);

...

Collectives™ on Stack Overflow

Convert string to date datatype in SQL Server in PHP Laravel

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related