CODEWITHSUNDEEP
sqlsql-servert-sql
Royce
Royce

Reputation: 1595

Data range with BETWEEN in WHERE clause with CONVERT is not filtering correclty

I'm using below SQL request to get information between 2 dates.

However result containing record out of the range I specified.

Query:

SELECT 
    CONVERT(VARCHAR, R.RN_EXECUTION_DATE, 103) AS 'Execution Date', 
    R.RN_TESTCYCL_ID, 
    R.RN_STATUS, 
    R.RN_RUN_NAME, 
    T.TS_USER_TEMPLATE_05, 
    RC.RCYC_NAME
FROM R 
    INNER JOIN TC 
        ON R.RN_TESTCYCL_ID = TC.TC_TESTCYCL_ID 
    INNER JOIN RC 
        ON RC.RCYC_ID = TC.TC_ASSIGN_RCYC 
    INNER JOIN T 
        ON R.RN_TEST_ID = T.TS_TEST_ID
WHERE R.RN_STATUS IN ('Passed', 'Failed')
    AND CONVERT(VARCHAR, R.RN_EXECUTION_DATE, 103) BETWEEN '16/11/2020' AND '28/02/2021'
    AND TC.CUSTOM_08 = 'IT5'
    AND (RC.RCYC_NAME LIKE '03_%TI-%' OR RC.RCYC_NAME LIKE 'SIT%')

Result:

Execution Date | ... | ... |
22/12/2020     | ... | ... |
22/01/2021     | ... | ... |
19/06/2020     | ... | ... |
22/07/2020     | ... | ... |
...            | ... | ... |

Data type

Data type of RN_EXECUTION_TIME is varchar

2018-04-04 00:00:00
2010-01-04 00:00:00
2020-12-10 00:00:00

Any error in my query?

Thank you in advance for your help

Upvotes: 0

Views: 300

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

AND CONVERT(VARCHAR, R.RN_EXECUTION_DATE, 103) BETWEEN '16/11/2020' AND '28/02/2021'

Huh? Why are converting a date to a string for date comparisons? Just use date comparisons. You can do:

CONVERT(DATE, R.RN_EXECUTION_DATE) BETWEEN '2020-11-16' AND '2021-02-28'

However, I prefer no conversions at all:

R.RN_EXECUTION_DATE >= '2020-11-16' AND
R.RN_EXECUTION_DATE < '2021-03-01'

Note that by getting rid of BETWEEN, this works regardless of whether the column has a time component.

EDIT:

Based on the examples of execution date in the question, you can use:

TRY_CONVERT(DATE, R.RN_EXECUTION_DATE) >= '2020-11-16' AND
TRY_CONVERT(DATE, R.RN_EXECUTION_DATE) < '2021-03-01'

Upvotes: 2

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

CONVERT(VARCHAR, R.RN_EXECUTION_DATE, 103) will return varchar value which you are trying to compare with date field.

Please try this:

SELECT 
    CONVERT(VARCHAR, R.RN_EXECUTION_DATE, 103) AS 'Execution Date', 
    R.RN_TESTCYCL_ID, 
    R.RN_STATUS, 
    R.RN_RUN_NAME, 
    T.TS_USER_TEMPLATE_05, 
    RC.RCYC_NAME
FROM R 
    INNER JOIN TC 
        ON R.RN_TESTCYCL_ID = TC.TC_TESTCYCL_ID 
    INNER JOIN RC 
        ON RC.RCYC_ID = TC.TC_ASSIGN_RCYC 
    INNER JOIN T 
        ON R.RN_TEST_ID = T.TS_TEST_ID
WHERE R.RN_STATUS IN ('Passed', 'Failed')
    AND R.RN_EXECUTION_DATE BETWEEN '16/11/2020' AND '28/02/2021'
    AND TC.CUSTOM_08 = 'IT5'
    AND (RC.RCYC_NAME LIKE '03_%TI-%' OR RC.RCYC_NAME LIKE 'SIT%')

Upvotes: 1

Related Questions