Flipping day and month in MM/DD/YYYY

Admin 1
Viewed 212

I have a column called subscription_start where the data looks as follows:

subsciption_start
1/1/2020
13/1/2020
20/9/2020

I should also note that it's currently stored as a STRING and not DATETIME for obvious reasons (database won't accept DATETIME when 20 is the first number because it's expecting only 1 through 12)

I am trying to flip around the second number and the first number, so I can then put it in a proper DATETIME format where the end result looks like this:

subscription_start
01-01-2020
01-13-2020
09-20-2020

So what I'm looking for is two-fold: Flip around the numbers and put it in a more proper DATETIME format.

postgresql sql
3 Answers

Don't do it. Store the date as a proper format:

select to_char(subscription_start, 'DD/MM/YYYY')

Or better yet, fix the data!

alter table t alter subscription_start type date using to_date(subscription_start, 'DD/MM/YYYY')

Don't store date as strings!

user_11440350

Best use to_date to parse the string and convert it to a date in one go:

SELECT to_date('20/9/2020', 'DD/MM/YYYY');

  to_date   
------------
 2020-09-20
(1 row)
user_64643080

This would convert the varchar column to datetime in the format as is

CONVERT(datetime, Subscription_Start, 103)

user_76130200
Related