Format options only apply when retrieving date, time and timestamps as strings. Supplying timestamp constants do not use that option and must comply with the standard ISO 8601 or the existing SQL standards (as seen in this ODBC Reference on the subject.

Some flexibility does exist. The <date part=""> separator can be either '/' or '-' and the separator between the <date part=""> and the <time part=""> can be either 'T' or a space (ie. ' ').

Your timestamp constant fails that in a couple of ways all surrounding your use of the '.' separator. You've used that as a separator for 3 different purposes: - the separator between the <date part=""> and the <time part=""> - as the <time element=""> separator (between the hours and the minutes for example) - as well as the <fraction> separator of which only the last one is understood (allowed).

I have seen this usage a few times before (maybe from DB2 on mainframes) but you will have to find a way to convert the value before submitting this as part of the SQL statement.

The TIMESTAMP_FORMAT option specifies the output (retrieval) format, not the input format.

Specifically, TIMESTAMP_FORMAT specifies the layout of the string that SQL Anywhere will use to hold the timestamp after it is retrieved from the database.

As far as input is concerned (converting from string to TIMESTAMP), any reasonable non-ambiguous format can be used, regardless of the current TIMESTAMP_FORMAT option. The DATE_ORDER option can be used to solve amibiguities involving day-month-year ordering on input.

In your case, the problem with your input string is the "-" between the DD and HH portions... that does not fall into the definition of "reasonable".

I can explain why your input string no longer works. SQL Anywhere now supports time zone offsets. Consider the following revised SQL statement.

select cast('2002-01-01-01.30' as timestamp with time zone)

The string represents midnight on January 1, 2002 in a time zone that is 1.5 hours west of UTC (somewhere in the Atlantic).

In your example (below), it looks like we are in UTC (-00.00) but the .00.000000 that follows is incorrect syntax (Note that SQL Anywhere is letting you get away with . as an alternative to the colon separator). Almost any symbol other than + or - as a separator would have been forgiven (,=*.$ etc.).

select cast('2002-01-01-00.00.00.000000' as timestamp with time zone)

And in anticipation of the argument, the date-time string converter does not know that you are casting the result to a timestamp (which has no time zone component).