Change format timestamp ( sql anywhere 16)

I try change the format to 'YYYY-MM-DD-HH.NN.SS.SSSSSS' with SET OPTION PUBLIC.timestamp_format = 'YYYY-MM-DD-HH.NN.SS.SSSSSS'

But when i try to update some timestamp fields ( UPDATE TUTU set dmaj = '2002-01-01-00.00.00.000000' ) -> impossible to convert '2002-01-01-00.00.00.000000'to timestamp.

My constrains is that I can't touch the sql request (so no cast), i can just modife sql data base configuration

asked 20 Apr '16, 08:28

ThibaultG's gravatar image

ThibaultG
41113
accept rate: 0%

Thanks for your answers. Some clients use sql anywhere 7 and with this version the timestamp format is YYYY-MM-DD-HH.NN.SS.SSSSSS . And my compagnie have sql anywhere 16 ... problem :(

(20 Apr '16, 10:16) ThibaultG

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.

permanent link

answered 20 Apr '16, 09:41

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
6.5k2895
accept rate: 29%

edited 22 Apr '16, 08:32

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".

permanent link

answered 20 Apr '16, 09:41

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

edited 20 Apr '16, 09:41

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).

permanent link

answered 21 Apr '16, 11:23

JBSchueler's gravatar image

JBSchueler
1.9k2836
accept rate: 16%

edited 21 Apr '16, 11:34

Thanks, but you know a solution for sql anywhere accepts this synthax ?

(22 Apr '16, 02:56) ThibaultG
1

Please read the answers again. IMHO they say: you won't be able to feed strings with that format directly into a timestamp column. A workaround might be to store those values in an additional char column and use a trigger to convert it into a timestamp value and write it into another column. But of course that depends if the application can support that.

(22 Apr '16, 07:35) Reimer Pods

Oki, so no solution for me :( . Thanks for the reponses :)

(22 Apr '16, 09:09) ThibaultG
Replies hidden

Based on Jack's explanation, I'd guess that SA 11.0.1 would still work for you, as it is the "newest" version that does not support time zones, IIRC... - Apparently, I can't tell whether that would be an option for you.

(22 Apr '16, 09:33) Volker Barth
1

You really haven't provided a lot of detail so it's difficult to offer workarounds. Your only example shows "hard-coded" SQL ( UPDATE TUTU set dmaj = '2002-01-01-00.00.00.000000' ) and, for reasons unexplained, you don't want to or can't change the input string format.

(22 Apr '16, 10:42) JBSchueler
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:

×190

question asked: 20 Apr '16, 08:28

question was seen: 437 times

last updated: 24 May '16, 06:40