select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI')

On 02/25/2011 09:30 AM, akp geek wrote:

Hi all -

I have recently upgraded from 8.3 to 9.0.2.

when I run the following sql in 9.0.2 " select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI') " I am getting the following
error and the sql runs fine in older version

Is it my installation issue or library missing?

ERROR: invalid value ":5" for "MI"
DETAIL: Value must be an integer.

********** Error **********

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

Thanks for the help
Regards

military time doesn't have the ':' in the HHMM.
look closely at your 'working' result, does it look like this?

reporting=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI');
to_timestamp
------------------------
2011-02-26 14:00:00-05
(1 row)

reporting=# select to_timestamp('02/26/2011 1450', 'MM/DD/YYYY HH24MI');
to_timestamp
------------------------
2011-02-26 14:50:00-05
(1 row)

Note the invalid time for the first result.

On Friday, February 25, 2011 6:41:54 am Reid Thompson wrote:

On 02/25/2011 09:30 AM, akp geek wrote:

Hi all -

I have recently upgraded from 8.3 to 9.0.2.

when I run the following sql in 9.0.2 " select
to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI') " I am getting the
following error and the sql runs fine in older version

Is it my installation issue or library missing?

ERROR: invalid value ":5" for "MI"
DETAIL: Value must be an integer.

********** Error **********

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

Thanks for the help
Regards

military time doesn't have the ':' in the HHMM.
look closely at your 'working' result, does it look like this?

reporting=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI');
to_timestamp
------------------------
2011-02-26 14:00:00-05
(1 row)

reporting=# select to_timestamp('02/26/2011 1450', 'MM/DD/YYYY HH24MI');
to_timestamp
------------------------
2011-02-26 14:50:00-05
(1 row)

Note the invalid time for the first result.

To confirm above I ran your query on 8.3.

Your original version:

test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI');
to_timestamp
------------------------
2011-02-26 14:00:00-08
(1 row)

Corrected for the ':':

test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24:MI');
to_timestamp
------------------------
2011-02-26 14:50:00-08
(1 row)

On 9.0.3 corrected for the ':':

test(5432)aklaver=>select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY
HH24:MI');
to_timestamp
------------------------
2011-02-26 14:50:00-08
(1 row)

Seems 9.0+ is stricter on its parsing.

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver wrote:

test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24MI');
to_timestamp
------------------------
2011-02-26 14:00:00-08
(1 row)

Corrected for the ':':

test=# select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY HH24:MI');
to_timestamp
------------------------
2011-02-26 14:50:00-08
(1 row)

On 9.0.3 corrected for the ':':

test(5432)aklaver=>select to_timestamp('02/26/2011 14:50', 'MM/DD/YYYY
HH24:MI');
to_timestamp
------------------------
2011-02-26 14:50:00-08
(1 row)

Seems 9.0+ is stricter on its parsing.

Agreed. We have made some cleanups to the to_timestamp code in recent
major releases and that is what the user must be seeing.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Thank you all for the suggestion and it's very clear to me now

Regards

On Fri, Feb 25, 2011 at 10:02 AM, Bruce Momjian <bruce@momjian.us> wrote: