Get first (and last) day of month - expression simplification

Question

I need to get date, which was 30 (later I will use also 90 or 18O) days before first day of current (and previous) month. I wrote this, but I think it's unnecessarily complicated, so I come here for help. It's any way how can I simplify this:

ib_encodedate(EXTRACT(YEAR from (dateadd(-30-datediff(day from cast(EXTRACT(MONTH from CURRENT_DATE) 
|| '-1-' 
|| EXTRACT(YEAR from CURRENT_DATE) as date) to date 'now') DAY to CURRENT_DATE))), EXTRACT(MONTH from (dateadd(-30-datediff(day from cast(EXTRACT(MONTH from CURRENT_DATE) 
|| '-1-' 
|| EXTRACT(YEAR from CURRENT_DATE) as date) to date 'now') DAY to CURRENT_DATE))),EXTRACT(DAY from (dateadd(-30-datediff(day from cast(EXTRACT(MONTH from CURRENT_DATE) 
|| '-1-' 
|| EXTRACT(YEAR from CURRENT_DATE) as date) to date 'now') DAY to CURRENT_DATE))))

In database I have dates in double, so I have to use function ib_encodedate to convert date to double and compare with date in database. Function have prototype:

ib_encodedate(INT year, INT month, INT day)

The same I need to write for last day of month.

Thanks for any help.

Answer 1

Seems like you need something like this:

SELECT
    DATEADD (-EXTRACT(DAY FROM CURRENT_DATE)+1 DAY TO CURRENT_DATE) AS FIRST_DAY_OF_MONTH,
    DATEADD (-30 DAY TO DATEADD (-EXTRACT(DAY FROM CURRENT_DATE)+1 DAY TO CURRENT_DATE)) AS A_MONTH_AGO,
    DATEADD (-90 DAY TO DATEADD (-EXTRACT(DAY FROM CURRENT_DATE)+1 DAY TO CURRENT_DATE)) AS THREE_MONTHS_AGO,
    DATEADD (-180 DAY TO DATEADD (-EXTRACT(DAY FROM CURRENT_DATE)+1 DAY TO CURRENT_DATE)) AS SIX_MONTHS_AGO
FROM 
    RDB$DATABASE

Using the function DATEADD from firebird you can easily accomplish this.