I've been slowly exploring adapting TIMESTAMP WITH TIME ZONE with our product and tonight did an initial test ALTERing a number of existing TIMESTAMP columns over. Our product immediately failed with the following error:

Cannot convert timestamp with time zone to a numeric

The SQL behind it appears to be: "select max(LastChanged) from dba.LocalSettings"

This command succeeds if I substitute "cast(LastChanged as timestamp)" but that kinda defeats the purpose. Is this intentional? The help files seem rather silent on how operations that succeed on timestamps would fail on timestamp-with-time-zone, some places even suggest that it would degrade to a simple timestamp when necessary.

If timestamp-with-time-zone is such a different animal from simple-timestamp then this is going to be a much more painful migration...

select @@version == 12.0.1.3406

asked 01 Feb '12, 21:35

Erik%20Anderson's gravatar image

Erik Anderson
42181223
accept rate: 15%


This issue was fixed last November in 12.0.1.3504

QTS 690734 : The MIN/MAX aggregates did not support TIMESTAMP WITH TIME ZONE values, XML, or varbit.

Previously, the MIN and MAX aggregate functions would return an error when using an argument of type "timestamp with time zone" or "xml":

Cannot convert timestamp with time zone to a numeric

or

Cannot convert xml to a numeric

Further, using an argument of type varbit (or long varbit) would convert the argument to type numeric before computing the aggregate.

This has been fixed. The MIN and MAX aggregates no longer attempt to cast these types to numeric.

You will need to download and apply a recent EBF.

permanent link

answered 01 Feb '12, 23:27

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266
accept rate: 40%

figures, thanks for the fast grep of the ChangeLog. Will try to do something later this week, hopefully I can disable enough stuff prevent a forced reboot.

(01 Feb '12, 23:32) Erik Anderson
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×37
×22
×5

question asked: 01 Feb '12, 21:35

question was seen: 1,094 times

last updated: 01 Feb '12, 23:32