0

I have a table in postgres called my_table.

In this table I have a column called date_text that is of text type and contains dates in the following format: 01-31-2020. I would like to directly convert this column from text type to date type. With the code below I'm able to get a column of date type. However, it is in the wrong format: 2020-01-31. How do I get it in the correct format (01-31-2020) and how do I update the column directly? I'm new to sql.

SELECT TO_Date(date_text,'DD-MM-YYYY') as date_date
FROM my_table;
sampeterson
  • 459
  • 4
  • 16
  • 2
    Dates have no format, they're binary types. Formats apply only when strings are parsed into dates, or dates are formatted as strings. What you see is how your client application displays the date. Even database tools are just clients. The ISO8601 format (YYYY-MM-DD) is the standard date format, unambiguous *and* sortable, which is why database tools use it to display dates – Panagiotis Kanavos Sep 03 '21 at 10:52

1 Answers1

2

Dates have no format, they're binary types. Formats apply only when strings are parsed into dates, or dates are formatted as strings.

What you see is how your client application displays the date. Even database management tools are just clients. The ISO8601 format (YYYY-MM-DD) is the standard date format, unambiguous and sortable, which is why database tools use it to display dates.

On the other hand, 04-07-2021 is unsortable and ambiguous. Is that April 7th or July 4th? It's way too easy to make mistakes this way, resulting in business problems, lost deadlines, flights, money, or far worse.

Imagine being the target of a police investigation because people used ambiguous localized date strings.

The real solution to your problem is to convert the text column into. a date column and have the client display it as it sees fit.

Even if you keep using TO_Date(date_text,'DD-MM-YYYY') you'll have to configure the client to format the dates.

Changing the type of the column to date is possible with ALTER TABLE ... SET DATA TYPE. Borrowing the answer from this question:

ALTER TABLE books 
ALTER COLUMN date_text SET DATA TYPE date
      USING to_date(date_text, 'mm-dd-yyyy');

I assume datet_text isn't the real name, otherwise the column may need renaming too. That in turn could break queries and views.

That's why it's a bad idea to specify the type in a column name, either as a prefix or suffix

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • I see your point. With that in mind I'll stick to the standard date format YYYY-MM-DD. How then do I change/update the date_text column directly, without having to add a new date column and delete the old date column? I prefer to keep the column at the same position as it is now in my_table – sampeterson Sep 03 '21 at 11:12
  • `update my_table set TO_Date(date_text, 'DD-MM-YYYY') as date_date;` doesn't work – sampeterson Sep 03 '21 at 11:22
  • `update my_table set date_date = to_date(date_text, 'DD-MM-YYYY');` – Stefanov.sm Sep 03 '21 at 12:00
  • From [this question[](https://stackoverflow.com/questions/60830842/postgresql-change-data-type-from-text-to-date) `ALTER TABLE books ALTER COLUMN publication_date SET DATA TYPE date USING to_date(publication_date, 'mm/dd/yyyy');` – Panagiotis Kanavos Sep 03 '21 at 12:08