format SQL date string

Question

I'm trying to get a date formatted between quotes on a select format() query:

select format('CREATE TABLE temporary_table AS
          SELECT id FROM table WHERE created >=%I ORDER BY 1 ASC LIMIT 1','2021-04-01');

I'm getting this:

CREATE TABLE temporary_table AS
              SELECT table FROM table WHERE created >="2021-04-01" ORDER BY 1 ASC LIMIT 1;

And I wanted to actually get the date between single quotes (as this won't work on an execute)

How can I achieve this?

score 1 · Accepted Answer · answered May 21 '21 at 15:24

For single-quoted values you need the specifier %L for format(). Like:

SELECT format('CREATE TABLE temporary_table AS
      SELECT id FROM table WHERE created >= %L ORDER BY 1 LIMIT 1','2021-04-01');

See:

Of course that only makes sense if you parameterize the input. You wouldn't bother to use format() for a constant date to begin with.

1 Answers1