Selecting a date range that dynamically excludes the current month in SQL

November 23, 2022

I’m looking to select data with a date range from an arbitrary point, say 2020-01-01 up until -but not including- the beginning of the current month, and allow this to be dynamic so regardless of when the query is run, it excludes values from the current month.

I attempted

SELECT date_column
FROM table
WHERE date_column >= '2020-01-01' and date_column < MONTH(GETDATE())
ORDER BY date_column DESC

but got the following error

QL compilation error: Can not convert parameter 'EXTRACT(month from GETDATE())' of type [NUMBER(2,0)] into expected type [TIMESTAMP_NTZ(9)]

How do I correct this?

>Solution :

use DATE_TRUNC to truncate the current date to month level value.

SELECT date_column
FROM table
WHERE date_column >= '2020-01-01' and date_column < DATE_TRUNC('MONTH', GETDATE())
ORDER BY date_column DESC

thus:

SELECT date_column
FROM (values 
    ('2022-11-23'::date),
    ('2022-12-23'::date),
    ('2022-10-23'::date)
      t(date_column)
)
WHERE date_column >= '2020-01-01' and date_column < DATE_TRUNC('MONTH', GETDATE())
ORDER BY date_column DESC

gives: