count the number of days of current month from day 1 until yesterday

Question

I am trying to calculate the number of days of the current of month from day 1 until yesterday without the need of changing the count manually. The original SQL as below:

select order_id 
from orders 
where date > dateadd(-23 to current_date) and date < 'today'

the desired code is something like

select order_id 
from orders 
where date > dateadd(datediff(day,firstdayofthemonth,current_date) to current_date) and date < 'today'

Appreciate any help

I notice that this is rather similar to your earlier question: [Get last month data from first day until last day in Firebird](https://stackoverflow.com/questions/47798555/get-last-month-data-from-first-day-until-last-day-in-firebird) — Mark Rotteveel, Jan 23 '20 at 13:29
You ask about _"days of the current month from day 1 until yesterday"._ Just to clarify, what do you want to happen when the query is run on the 1st of a month? Then "yesterday" will no longer be in the "current month". Do you want zero results? Do you want the previous month in its entirety? — pilcrow, Jan 23 '20 at 15:23

score 2 · Accepted Answer · answered Jan 23 '20 at 13:08

2

In firebird you could do:

WHERE 
    date >= DATEADD(1 - EXTRACT(DAY FROM CURRENT_DATE) DAY TO CURRENT_DATE) 
    AND date < CURRENT_DATE

answered Jan 23 '20 at 13:08

GMB

216,147
25
84
135

Mark Rotteveel · Answer 2 · 2020-01-24T09:42:36.263

1

In addition to the answer provided by GMB, you can also use fact that Firebird allows addition of days to a date without needing to use dateadd:

date > current_date - extract(day from current_date) 
and date < current_date

edited Jan 24 '20 at 09:42

answered Jan 23 '20 at 13:32

Mark Rotteveel

100,966
191
140
197

Arioch 'The · Answer 3 · 2020-01-24T09:36:45.390

In addition to the answer provided by Mark, you can also use BETWEEN (starting with Firebird 2.0.4)

WHERE 
   date BETWEEN current_date - extract(day from current_date) + 1 
            AND current_date - 1

P.S. all those answers rely upon DATE data type (thus, date column and CURRENT_DATE variable) having no time part. Which is given for modern SQL dialect 3. But if Dialect 1 would get used it is not given.

count the number of days of current month from day 1 until yesterday

3 Answers3