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

Occasionally in psql we need a function that returns the first and last day of the month for a given date. This seems simple, after all we know that the beginning of the month will always be '01', very different from the last day of the month which can vary depending on the month and year. In the function below we create a function that will return the first day of the month for a given date and return it in timestamp format. This function will work in conjunction with the function also explained in another article called... GET_LAST_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_FIRST_DAY_OF_MONTH ( P_DATE_REF timestamp) returns timestamp as declare variable LCALC_DATE timestamp; begin -- Calculates the beginning of the month of the given date lcalc_date=:P_DATE_REF; select cast(:P_DATE_REF as date) - extract(day from cast(:P_DATE_REF as date)) + 1 from RDB$DATABASE into :lcalc_date; return :lcalc_date; end<\/pre><\/div>\n\n\n\n
How to use<\/h2>\n\n\n\n
select GET_FIRST_DAY_OF_MONTH(current_timestamp) as first_day_of_month from 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-2260","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