Ao trabalhar com datas SQL, às vezes você precisa calcular o fim do mês. Os meses são complicados! Alguns são 28 dias, outros 30 ou 31, e de vez em quando há um ano bissexto!
Então, dada uma data, como se calcula o número de dias restantes no mês?
O cálculo é realmente um processo de dois passos:
- Determinar a última data do mês.
- Calcular a diferença em dias, entre a data em questão e o passo 1.
Podemos usar a função DATEDIFF calcular a diferença em dias, mas como se calcula o último dia do mês?
Usar EOMONTH para calcular o fim do mês
No SQL Server 2012 ou superior, pode usar a função EOMONTH para retornar o último dia do mês.
Por exemplo
SELECT EOMONTH('02/04/2016')
Retorna 02/29/2016
Como você pode ver a função EOMONTH leva em conta o ano bissexto.
Para calcular o número de dias de uma data até o final do mês você poderia escrever
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Que retorna 25.
Vamos tentar um exemplo mais abrangente que calcula os dias restantes em um LoanDate para o mês atual do LoanDate:
BEGIN TRANSACTION-- Sample DataCREATE TABLE LoanDate (LoanID INT, LoanDate DATE);INSERT INTO LoanDate Values (1, '1/1/2016');INSERT INTO LoanDate Values (1, '1/15/2016');INSERT INTO LoanDate Values (1, '1/31/2016');INSERT INTO LoanDate Values (1, '2/15/2016');INSERT INTO LoanDate Values (1, '3/15/2016');INSERT INTO LoanDate Values (1, '4/15/2016');INSERT INTO LoanDate Values (1, '5/15/2016');INSERT INTO LoanDate Values (1, '6/15/2016');INSERT INTO LoanDate Values (1, '7/15/2016');INSERT INTO LoanDate Values (1, '8/15/2016');INSERT INTO LoanDate Values (1, '9/15/2016');INSERT INTO LoanDate Values (1, '10/15/2016');INSERT INTO LoanDate Values (1, '11/15/2016');INSERT INTO LoanDate Values (1, '12/15/2016');-- Select LoanDate, Days in Month, and Days Remaining in MonthSELECT LoanID, LoanDate, EOMONTH(LoanDate) LoanDateEndOfMonth, DATEDIFF(d, LoanDate, EOMONTH(LoanDate)) as DaysRemainingFROM LoanDateROLLBACK
Aqui está o resultado:
Como você pode ver, o DaysRemaining muda de acordo com o mês. Observe também que quando a data ocorre na mesma data do final do mês, como na linha 3, permanecem zero dias.
Agora vamos ver como você calcularia isso se você estiver usando SQL 2008 R2 ou antes.
Old School Method to Calculate Last Day of Month
O cálculo é realmente o mesmo processo de dois passos:
- Determinar a última data do mês
- Calcular a diferença em dias, entre a data em questão e o passo 1.
No entanto, a diferença está em como determinamos a última data do mês. Como o EOMONTH não está disponível, precisamos calculá-lo da forma “antiquada”. Há vários métodos para fazer isso. Aqui está um.
Calcularemos o último dia do mês usando duas funções: DATEADD e DAY.
Utilizaremos DATEADD para adicionar um mês à data. Em seguida, a função DIA para determinar o número de dias a partir do início do mês. Subtraindo isto da data que acabamos de calcular (a que está um mês à frente), podemos obter a última data do mês.
OK, eu sei que isso parece confuso, então vamos decompor usando este diagrama:
Usando o mesmo exemplo que fizemos com EOMONTH temos a seguinte afirmação que podemos usar para testar:
BEGIN TRANSACTION-- Sample DataCREATE TABLE LoanDate (LoanID INT, LoanDate DATE);INSERT INTO LoanDate Values (1, '1/1/2016');INSERT INTO LoanDate Values (1, '1/15/2016');INSERT INTO LoanDate Values (1, '1/31/2016');INSERT INTO LoanDate Values (1, '2/15/2016');INSERT INTO LoanDate Values (1, '3/15/2016');INSERT INTO LoanDate Values (1, '4/15/2016');INSERT INTO LoanDate Values (1, '5/15/2016');INSERT INTO LoanDate Values (1, '6/15/2016');INSERT INTO LoanDate Values (1, '7/15/2016');INSERT INTO LoanDate Values (1, '8/15/2016');INSERT INTO LoanDate Values (1, '9/15/2016');INSERT INTO LoanDate Values (1, '10/15/2016');INSERT INTO LoanDate Values (1, '11/15/2016');INSERT INTO LoanDate Values (1, '12/15/2016');-- Select LoanDate, Days in Month, and Days Remaining in MonthSELECT LoanID, LoanDate, DATEADD(dd,-(DAY(DATEADD(mm,1,LoanDate))), DATEADD(mm,1,LoanDate)) LoanDateEndOfMonth, DATEDIFF(d,LoanDate, DATEADD(dd,-(DAY(DATEADD(mm,1,LoanDate))), DATEADD(mm,1,LoanDate))) as DaysRemainingFROM LoanDateROLLBACK
Aqui estão os resultados:
Então qual método você deve usar?
Se estou escrevendo código e sei que será executado no SQLServer 2012 ou superior, eu iria para o método EOMONTH porque é muito mais fácil de ler; no entanto, acho que conhecer e entender o método usando DATEADD e DAY ajuda a entender melhor como manipular datas.