Question [SOLVED] SQL Date conversion questions - find first day of month

[Ryszard Musielak]

Hello Everyone,

here is my question. I need to run a SQL query with a where clause against a date field. I have to run this "today", lets say
every 1st day of month and generate a report for last month from 1st till End of month.

This I can do to extract the last months:

select  to_char(ADD_MONTHS (curdate(),-1)) ,  LAST_DAY(ADD_MONTHS (curdate(),-1))  from ...;
to_char(add_months(curdate,-1*1)) last_day(add_months(curdate,-1*1))
--------------------------------- ----------------------------------
01/04/2022                        2022-01-31

I ran this today (4th of Feb), so if I ran this on the 1st of each month, it would work, but I'd like to be able to actually make it into a nice code and be
able to run it again if it fails on the 1st, something like :
From FISRT_DAY (xxx) to LAST_DAY (xxx).

There is no FIRST_DAY function. I am trying to convert this somehow, but SQL doesn't like this:

...
WHERE sh."o-date" BETWEEN To_char(ADD_MONTHS (curdate(),-1),'monyy') AND LAST_DAY(ADD_MONTHS (curdate(),-1))

Any suggestions?

I found this web site, but no clues there:

Date/Time Functions

www.vertica.com

This is another of my sources:

Progress Documentation

docs.progress.com

Richard

 

[Stefan]

The first day of a month is equal to the last day of the previous month plus one.

 

[Ryszard Musielak]

I tried this and it works:
WHERE sh."o-date" BETWEEN TO_DATE(To_char(ADD_MONTHS (curdate(),-1),'01-mm-yyyy')) AND LAST_DAY(ADD_MONTHS (curdate(),-1))
First day hardcoded. Not beautiful, but works.

The first day of a month is equal to the last day of the previous month plus one.

Oh, you mean this?

select LAST_DAY(ADD_MONTHS (curdate(),-2))+1 , LAST_DAY(ADD_MONTHS (curdate(),-1)) from sysprogress.syscalctable;

Seems to work, looks awful, but native

Thanks