How to get the trasaction that happens in 2020,2021 in SQL Server?-CodePudding

I have a table with columns id(int) and trans_id string.

trans_id contains values such as 20345,19345 - the 1st 2 chars represent years, I want a query for transactions that happened in 2020,2019

CodePudding user response：

You should store dates in a date or datetime column, not as a string or integer. And you certainly shouldn't store multiple values in one column.

Assuming trans_id is an int you can do

SELECT *
FROM YourTable t
WHERE trans_id >= 19000 AND trans_id < 21000;

If trans_id is a varchar string, you can do

SELECT *
FROM YourTable t
WHERE trans_id LIKE '20%' OR trans_id LIKE '19%';

If you've gone for an even worse version and stored multiple values, you need to split them first

SELECT *
FROM YourTable t
WHERE EXISTS (SELECT 1
    FROM STRING_SPLIT(trans_id, ',') s
    WHERE s.value LIKE '20%' OR s.value LIKE '19%'
);

CodePudding user response：

You can also use LEFT to get the first two characters of the string.
Then use IN for the list of years you need.

SELECT *
FROM YourTable t
WHERE LEFT(trans_id, 2) IN ('19', '20')

But don't use BETWEEN without casting the 2 digits to an INT.