to_date with regex in postgresql

Question

I've got a column of varchar type, it contains dates in the format of 8-Jun-12 and 18-Jun-12

I have an empty column of date type

I'd like to fill up my column with the dates as actual dates. I was hoping to get away with something like:

SET formatted_date=to_date(mydatecol, ('[0-9]{1,2}-[a-zA-Z]{3}-[0-9]{2}'));

But my resulting column is null.

GMB · Accepted Answer · 2020-09-09 23:17:06Z

1

Use to_date():

update mytable
set formatted_date = to_date(mystring, 'dd-mon-yy')

answered Sep 9, 2020 at 23:17

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

van Over a year ago

oh man I feel so dumb, thanks. 11 mins and I can accept the answer

marcothesane · Accepted Answer · 2020-09-09 23:24:33Z

0

PostgreSQL?

You can just hard-cast it:

WITH 
inp(dts) AS (
          SELECT '8-Jun-12'
UNION ALL SELECT '18-Jun-12'
)
SELECT
  dts
, dts::DATE 
FROM inp;
-- out     dts    |    dts     
-- out -----------+------------
-- out  8-Jun-12  | 2012-06-08
-- out  18-Jun-12 | 2012-06-18

answered Sep 9, 2020 at 23:24

marcothesane

6,8421 gold badge13 silver badges24 bronze badges

Collectives™ on Stack Overflow

to_date with regex in postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related