{"id":2267,"date":"2023-08-04T16:50:23","date_gmt":"2023-08-04T19:50:23","guid":{"rendered":"https:\/\/gladiston.net.br\/?page_id=2267"},"modified":"2023-08-07T10:43:38","modified_gmt":"2023-08-07T13:43:38","slug":"pegando-o-ultimo-dia-do-mes-de-uma-determinada-data","status":"publish","type":"page","link":"https:\/\/gladiston.net.br\/en\/banco-de-dados\/pegando-o-ultimo-dia-do-mes-de-uma-determinada-data\/","title":{"rendered":"Taking the last day of the month from a given date"},"content":{"rendered":"

Vez ou outra em psql precisamos de uma fun\u00e7\u00e3o que retorne o primeiro e o \u00faltimo dia do m\u00eas de uma determinada data. Isso parece simples, afinal sabemos que o inicio do m\u00eas ser\u00e1 sempre ser\u00e1 o dia \u201901\u2019, muito diferente do \u00faltimo dia do m\u00eas que pode variar conforme o m\u00eas e o ano. Na fun\u00e7\u00e3o abaixo criamos uma fun\u00e7\u00e3o que retornar\u00e1 o ultimo dia do m\u00eas de uma determinada data e retorne em formato do tipo timestamp. Essa fun\u00e7\u00e3o trabalhar\u00e1 em conjunto com a fun\u00e7\u00e3o tamb\u00e9m explicada em outro artigo chamada de GET_FIRST_DAY_OF_MONTH<\/a>.<\/p>\n\n\n\n

If you've ever had a need like this, the psql function below will solve your problem:<\/p>\n\n\n\n

create or alter function GET_LAST_DAY_OF_MONTH (\n    P_DATE_REF timestamp)\nreturns timestamp\nas\ndeclare variable LCALC_DATE timestamp;\ndeclare variable LCALC_NEXT_MONTH timestamp;\nbegin\n  -- Calcula o pr\u00f3ximo m\u00eas\n  LCALC_NEXT_MONTH=dateadd(month, 1, :P_DATE_REF);\n  -- Pega o inicio do pr\u00f3ximo mes\n  select cast(:LCALC_NEXT_MONTH as date) - extract(day from cast(:LCALC_NEXT_MONTH as date)) + 1\n  from RDB$DATABASE\n  into :lcalc_date;\n\n  -- agora subtrai 1 segundo\n  lcalc_date=dateadd(second, -1, :lcalc_date);\n  return :lcalc_date;\nend<\/pre><\/div>\n\n\n\n
How to use<\/p>\n\n\n\n
select\n  GET_LAST_DAY_OF_MONTH(current_timestamp) as primeiro_dia_do_mes \nfrom RDB$DATABASE<\/pre><\/div>\n\n\n\n
Imagine searching for the closing period of the current month:<\/p>\n\n\n\n
select * from table a where a.dt_lancamento between GET_FIRST_DAY_OF_MONTH(current_timestamp) and GET_LAST_DAY_OF_MONTH(current_timestamp) <\/pre><\/div>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
Functions to retrieve the start and end of the month may seem trivial at first, but trust me, when programming with psql you will have times when you need these dates to process something important that must be counted from the beginning of the month to its end, or both. For example, consider querying entries for a monthly period, from the 1st to the end of the month, including the time suffix 23:59:59 to avoid missing transactions from the last day. The fact that the function returns a timestamp will also help you with the index, which will not require a timestamp cast to date.<\/p>","protected":false},"excerpt":{"rendered":"
Vez ou outra em psql precisamos de uma fun\u00e7\u00e3o que retorne o primeiro e o \u00faltimo dia do m\u00eas de uma determinada data. Isso parece simples, afinal sabemos que o inicio do m\u00eas ser\u00e1 sempre ser\u00e1 o dia \u201901\u2019, muito diferente do \u00faltimo dia do m\u00eas que pode variar conforme o m\u00eas e o ano. […]<\/p>\n","protected":false},"author":4,"featured_media":0,"parent":1109,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"templates\/template-full-width.php","meta":{"footnotes":""},"class_list":["post-2267","page","type-page","status-publish","hentry"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t