How to do a query that only refers to the DIA of a date?

Question

I have a MYSQL bank and I need an SQL that refers to the PAYMENT DAY in the DAY field (this field will only have the DAY of the month that the client chose to make the payment, this field is of the INT type) and show me some record whenever less than 5 days to the payment day are missing. Of course this DIA field will be based on the current date.

I have this code:

Select * from conta where DAYOFMONTH(CURDATE()) - 5 = dia;

Only that does not work, because it shows only the records that are 5 days to maturity, ie if missing 4 days, 03 days, 02 days, 01 day it shows nothing. I need something that always shows some result when there are less than 5 days left to pay.

Answer 1

Select * from tabela where DAYOFMONTH(CURDATE())-5 = colunaDia;

Answer 2

beauty ?

Man, I don’t know what your basic structure is, but just using payday makes it a lot harder to validate. Look at one situation, today is the 29th, if I go to consider everyone who has less than 5 days to win would have to catch everyone who goes from today until the 3rd of April. So if your customer has marked that day 1 is his payment day you will have to consider that "29" - "1" will meet your 5-day rule as you are passing your doubt that you have option to work only with the current date and a full day field.

However...

If you want to control salaries, it would be interesting to have a table with all launches with their respective salaries forward.

Ex:

Customer wants to pay something, today 29/03, in 3x and his payment day will be all day 10.

soon

I have a table where I will launch three moves for future collection with expiration of: 10/04/2016, 10/05/2016 and 10/06/2016.

This makes it easy and simple to take the difference of the dates and check if the amount of days is less than the 5 you want.

Anything explains a little more how your table structure is, without knowing the bank is a little more complicated to help.

If my suggestion was helpful, signal there! Thanks!

Answer 3

You can use the DATEDIFF to do this.

In the function you have to use the current date in the first parameter in the second you add the total of days you check the current date ie 5 . DATEADD(day,5, getdate()) with the days added just do the DATEDIFF

DATEDIFF(day, GETDATE() , DATEADD(day,5, getdate()))

declare @Usuario table
(
  nome varchar(20),
  diapagamento int
)


insert into @Usuario values
( 'zé', 5),
( 'fulano', 15),
( 'manoel', 25),
( 'joao', 30),
( 'carlos', 5),
( 'zpauloé', 2)


select *  from @Usuario
where diapagamento <= DATEDIFF(day, GETDATE() , DATEADD(day,5, getdate()))

slideshow <= -- here will return all users where missing 5 or less days to the day of payment.

Well, this is in sql server but the idea is the same.

Select * from conta 
where dia <= DATEDIFF(DATE_ADD(CURDATE(),INTERVAL 5 DAY), CURDATE())
--where DAYOFMONTH(CURDATE()) - 5 = dia;