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?

Erwin Brandstetter · Accepted Answer · 2021-05-21 15:24:48Z

1

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.

answered May 21, 2021 at 15:24

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1