Question 312772 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: how to convert text date to show in the format of 'YYYY-MM-DD'

I was taking an online exam on postgre SQL. The first step is about cleaning data and there is a column named ‘date1’ in the text datatype and has a format as: YYYY-MM-DDT00:00:00.000.
The requirement is to convert it to date (data type) and show it in the format of YYYY-MM-DD.
The requirement is not to update the table, just query the table as the instruction.

What I did is:
SELECT CAST(date1 AS DATE) AS date1
FROM tablename

However, when the table is displayed, it still shows date1 column in the format as:
YYYY-MM-DDT00:00:00.000
I thought once it’s converted to date datatype, it should not show the time part anymore. Can someone help? Thank you very much!

Tags:

2

Answers


  1. 0

    Here is an alternative solution that uses the To_Char function if you were interested

    SELECT TO_CHAR(CAST('2024-01-02T14:28' AS DATE), 'YYYY-MM-DD');
    Login or Signup to reply.
  2. 0

    Since version 16 you can use the function pg_input_is_valid() to check if the content is valid, to avoid errors when casting the content to the correct data type. And since a DATE only contains the year, month and day of the month, you will never end up with the minutes or seconds.

    SELECT CAST(date1 AS DATE) AS date1
FROM tablename
WHERE pg_input_is_valid(date1, 'date');

    If you do see a time in your result, that must be your SQL client that makes a mess of the result. That would be something you have to fix in your client, since the database is doing everything right.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search