CODEWITHSUNDEEP
sqlstringdatetimeamazon-redshiftwhere-clause
Churku
Churku

Reputation: 57

Amazon Redshift - variable in where condition

I'm trying to automate a query filter. So, the value of the filter keeps changing each month.

Select * from table where desc like '202012%'

The following command gets me 202012%, but I'm not able to figure out if/how I can use it in a where clause concat(extract(year from current_date) , lpad(extract(month from current_date), 2)) || '%'

Thank you.

Upvotes: 0

Views: 477

Answers (1)

GMB
GMB

Reputation: 222462

Do you want to_char()?

where descr like to_char(current_date, 'YYYYMM') || '%'

Important: your questions suggests that you are storing dates as strings. Don't! Use the proper datatype to store your data: this is more efficient, and much safer (data integrity is guaranteed). Then, you can use date functions, like so:

where descr >= date_trunc('month', current_date) 
  and descr <  dateadd(month, 1, date_trunc('month', current_date))

Side note: desc is a language keyword, hence not a good choice for a column name. I renamed it to descr in the code snippets.

Upvotes: 0

Related Questions