how to change date into DD/MM/YYY format and then into text using postgresql?

Question

select
    dt.client_id,
    dt.user_id,,
    CAST(DATE(dt.created_on) AS TEXT) as created_on,
    dt.created_by,
    dt.last_update_on,
    dt.last_update_by
from
    draft dt
where
    dt.user_id = 'ABC'
    and CAST(DATE(dt.created_on) AS TEXT) LIKE LOWER('%2020-10-30%')

here created_on is datetime column which need to formatted first into YYYY-MM-DD format then need to convert into text

Please provide sample data and desired results. Your question is also quite unclear. — Gordon Linoff
– Gordon Linoff, Commented Feb 6, 2021 at 20:03
You cannot change a date into DD/MM/YYYY, and then into text because, as soon as you are applying a format it becomes text. (and no longer is a date, it might represent a date, but it is text!) — Luuk
– Luuk, Commented Feb 6, 2021 at 20:14

user330315 · Accepted Answer · 2021-02-06 20:05:31Z

1

To format the value, us to_char():

to_char(dt.created_on, 'yyyy-mm-dd') as created_on

But you shouldn't use LIKE for DATE (or TIMESTAMP) values.

Assuming created_on is a timestamp (there is no datetime data type in Postgres) you can cast it to a date to compare it with a date value:

where cast(dt.created_on as date) = date '2020-10-30'

For performance reasons using a range condition on the timestamp column might be better if it's indexed:

where dt.created_on >= date '2020-10-30' 
  and dt.created_on < date '2020-10-30' + 1

answered Feb 6, 2021 at 20:05

user330315

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

Collectives™ on Stack Overflow

how to change date into DD/MM/YYY format and then into text using postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related