Selecting between two dates

Question

I was trying to select data between to dates starting from March 1 to March 31 of 2021 but it shows data from March 2020. This is the query I am using now:

SELECT  
    ROW_NUMBER() OVER (ORDER BY T.Payment_Date DESC),
    FORMAT (Entry_Date, 'dd/MM/yyyy ') 'Entry Date',
    FORMAT (Payment_Date, 'dd/MM/yyyy ') 'Paid Date' 
FROM
    Transes T 
WHERE 
    Trx_Status = 1 
    AND Remit_Status = 3 
    AND (CONVERT(varchar(50), T.Payment_Date, 101) BETWEEN '03/01/2021' AND '03/31/2021') 
ORDER BY
    T.Payment_Date DESC

The problem: instead of selecting between '03/01/2021' and '03/31/2021', it adds data from March 2020, it selects from 03/01/2021 to 03/27/2020 but only March Month.

What is the problem? Please help.

Gordon Linoff · Accepted Answer · 2021-04-01 00:55:53Z

1

Do not use string functions on dates! Simply express the conditions as:

WHERE Trx_Status = 1 and
      Remit_Status = 3 and
      T.Payment_Date >= '2021-03-01' and
      T.Payment_Date < '2021-04-01'

Note that I changed the date comparisons to use >= and <. This is safer than BETWEEN, because it works for dates and datetimes with no changes.

answered Apr 1, 2021 at 0:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Elias Over a year ago

Thank you very much, I thought that was the problem and I got it by just removing the string function. Thanks Mate.

Elias Over a year ago

why this T.Payment_Date >= '03/01/2021' and T.Payment_Date <= '03/31/2021' doesn't work?, I am avoiding to use 04/01/2021.

Charlieface Over a year ago

@Elias because it has a time component as well. Note Gordon's correct use of a half-open interval >= AND <

Collectives™ on Stack Overflow

Selecting between two dates

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related