CODEWITHSUNDEEP
sqlpostgresqldatedatetime
sampeterson
sampeterson

Reputation: 479

Convert date column from text data type to date type in sql

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;

Upvotes: 0

Views: 5087

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131237

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

Upvotes: 2

Related Questions