How do I select between 1st day of the current month and the 1st day of the following month (from cu-CodePudding

SELECT tbl.createdate > '20211001' and tbl.createdate < '20211101'

I need the dates in the '' to change depending on the date the query is run. For example if I schedule it to run in November it would be SELECT tbl.createdate > '20211101' and tbls.createdate < '20211201'

and if I run it in December it would be SELECT tbl.createdate > '20211201' and tbl.createdate < '20220101'

I need the query to run without user changing the dates.

CodePudding user response：

Your can use the date_trunc() function, see the manual. The result is :

SELECT *
FROM tbl 
WHERE tbl.createdate > date_trunc('month', Now()) 
  and tbl.createdate < (date_trunc('month', Now())   interval '1 month')