Documentation says SWITCHOFFSET( tmz-expression, time-zone-offset ) but in it is only working with SWITCHOFFSET( tmz-expression, integer in minutes )

Is this already fixed/changed?

asked 27 Sep '16, 08:27

Martin's gravatar image

accept rate: 14%

What is exactly broken in your estimate here?

The example given for this function works as documented in 16 (your build or the most recent) and 17 for me. Even when I specify both hours and minutes with

"SWITCHOFFSET( EDT,'-07:30' )"

It is possible you are supplying it as an integer?

(27 Sep '16, 09:22) Nick Elson S...
Replies hidden

It is possible you are supplying it as an integer?

If so, that would also be as documented:

The value can be an integer representing the minutes before or after Coordinated Universal Time (UTC), a string in the form { + | - } hh:nn, or Z for the Zulu Time Zone. The Zulu Time Zone is the same time zone as UTC.

(27 Sep '16, 09:53) Volker Barth

Maybe you can show us what you mean then?

It seems to work for me (either way). For example:

SELECT CAST ( '2009-04-03 14:45:12.123-04:00' AS datetimeoffset ) AS EDT, SWITCHOFFSET( EDT,-420 ), SWITCHOFFSET( EDT,420 );


EDT                            ,SWITCHOFFSET(EDT,-420)         ,SWITCHOFFSET(EDT,420)
'2009-04-03 14:45:12.123-04:00','2009-04-03 11:45:12.123-07:00','2009-04-04 01:45:12.123+07:00'
(27 Sep '16, 10:28) Nick Elson S...

SELECT SWITCHOFFSET(current time,'-07:00')

leads to the error:

ERROR [07006] [Sybase][ODBC Driver][SQL Anywhere]Cannot convert '-07:00' to a integer

So you have to use an integer and cannot as described use a textual representation

(28 Sep '16, 03:35) Martin

See above the usage of a string is not possible

(28 Sep '16, 03:36) Martin

This seems to be related to the first expression which is not a TIMESTAMP WITH TIME ZONE data type altough SWITCHOFFSET expects that.

The following fails for me on (with a -157 SQLCODE as in your test):

select switchoffset(current timestamp, '-07:00');

whereas the following work as expected:

select switchoffset(current utc timestamp, '-07:00');
select switchoffset(cast(current timestamp as timestamp with time zone), '-07:00');

select switchoffset(current utc timestamp, -420);
select switchoffset(current timestamp, -420);
select switchoffset(cast(current timestamp as timestamp with time zone), -420);

So I think you have to cast the first expression to the expected data type...

permanent link

answered 28 Sep '16, 03:57

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 28 Sep '16, 04:27

Funny that the wrong input works with an integer as the second parameter

(28 Sep '16, 05:48) Martin
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 27 Sep '16, 08:27

question was seen: 154 times

last updated: 28 Sep '16, 05:48