PostgreSQL unable to parse string to date using to_date()

Question

I have a pretty standard date as a string that I need to parse to a date type:

"2016-06-01T23:34:25+00:00"

I'm using 'YYYY-MM-DDTHH24:MI:SS' as the format mask and this query to try to get and parse the date:

SELECT to_date('last_updated_on', 'YYYY-MM-DDTHH24:MI:SS') 
AS last_updated_on FROM locations 
limit 1

What happens is I get this error:

ERROR: invalid value ":2" for "MI"
SQL state: 22007
Detail: Value must be an integer.

I've looked all over in the documentation and on SO trying to find out why this is happening, and I'm totally baffled.

I'm using PostgreSQL 9.4

Vamsi Prabhala · Accepted Answer · 2016-06-02 01:43:01Z

6

Surround the T in format string with "". If the time part of the date needs to be preserved, use to_timestamp().

SELECT to_date('last_updated_on', 'YYYY-MM-DD"T"HH24:MI:SS') 
AS last_updated_on FROM locations 
limit 1

Converting to date with timestamp

SELECT to_timestamp('last_updated_on', 'YYYY-MM-DD"T"HH24:MI:SS') 
AS last_updated_on FROM locations 
limit 1

edited Jun 2, 2016 at 1:43

answered Jun 2, 2016 at 1:28

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

1 Comment

Kyle Falconer Over a year ago

Thanks! I suspected that T was the culprit, but didn't know how to define it as a literal "T"

Gordon Linoff · Accepted Answer · 2016-06-02 01:29:20Z

4

The problem would appear to be the T n the middle of the string. This seems tow work:

SELECT to_date(replace('2016-06-01T23:34:25+00:00', 'T', ''),  'YYYY-MM-DD HH24:MI:SS')

However, you probably want to_timestamp():

SELECT to_timestamp('2016-06-01T23:34:25+00:00', 'YYYY-MM-DD HH24:MI:SS')

Also note that you have single quotes around last_updated_on. That means that it is interpreted as a string, not a column name.

answered Jun 2, 2016 at 1:29

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Kyle Falconer Over a year ago

Awesome. Thanks! Yeah I put that last_updated_on in when writing my question. The value that goes in there comes from a jsonb value, which I took out to try to clarify the meaning of the question.

Collectives™ on Stack Overflow

PostgreSQL unable to parse string to date using to_date()

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related