WHERE date BETWEEN today and next 30 days not working-CodePudding

I'm having a bit of trouble with pulling records which are due within 30 days. The database I am working with stores the date in a char(10) field in format 103 (dd/mm/yyyy or 10/12/2021). I use a convert function to make this date usable, but when I try to use it with a between query it fails:

WHERE 
    CONVERT(Date, SUBSTRING(TDate, 1, 10), 103) 
        BETWEEN DATEADD(DAY, 30, GETDATE()) AND GETDATE()

Now I suspect that it fails because GETDATE() defaults to format yyyy-mm-dd-time, so the comparison won't work. My question is, how can I convert GETDATE() to format 103 to get the correct comparison, or is it a matter of converting my TDate field to something else to get it working?

CodePudding user response：

The expression x BETWEEN a AND b is same as x >= a AND x <= b.

Now GETDATE() 30 is always going to be greater than GETDATE() so this condition can never be true for any value — just like x >= (y 30) and x <= y cannot be true for any x and y.

Then we have another problem, you're comparing a date with datetime. If the date is 2021-12-10 and current datetime is 2021-12-10 12:00 PM then the comparison will return false when you check date >= datetime. I recommend the following:

WHERE CONVERT(DATE, tdate, 103) >= CAST(GETDATE() AS DATE)
AND   CONVERT(DATE, tdate, 103) <= DATEADD(DAY, 29, CAST(GETDATE() AS DATE)) -- the range [0, 29] contains 30 days