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