Ryszard Musielak
Member
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:
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:
Any suggestions?
I found this web site, but no clues there:
This is another of my sources:
Richard
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:
Code:
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:
Code:
...
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
Progress Documentation
docs.progress.com
Richard