Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

According to the documentation for DATEFORMAT / timestamp_format, MM represents month.

However, consider the output of:

SELECT DATEFORMAT('2017-12-30 12:34:56.789','YYYY:MM:DD:HH:NN:SS:SSS')

The expected value would be:

'2017:12:30:12:34:56:789'

However, the function returns:

'2017:34:30:12:34:56:789'

It appears that the database server (dbsrv12 version 17.0.4.2053, also tested on the last 12.0.1 EBF with same result) considers MM to be an alias for NN if preceded by a colon. That is not the documented behavior. I recent ran into an application that required all date parts to be separated by colons.

Of course, the workaround is YEAR(...)||':'||MONTH(...)||... but if the existing behavior is by design, should the documentation not reflect that? Or is this a bug? I presume that since the behavior seems to be specific to colons, that it is intentional.

asked 30 Mar '17, 11:35

mmellon's gravatar image

mmellon
1464410
accept rate: 25%

1

Can't tell on the intent/bug issue, but using a standard format and then replacing the delimiter might also do the trick...

SELECT REPLACE(DATEFORMAT('2017-12-30 12:34:56.789','YYYY.MM.DD:HH:NN:SS:SSS'), '.', ':')

returns '2017:12:30:12:34:56:780' (note the final zero) with v12.0.1.4403.

(30 Mar '17, 12:06) Volker Barth

This is documented behavior under the TIMESTAMP_FORMAT DCX article

"MM Two-digit month, or two-digit minutes if following a colon (as in HH:MM) "

permanent link

answered 30 Mar '17, 12:11

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

Nick Elson S...
7.3k35107
accept rate: 32%

Yep. I must be blind.

(31 Mar '17, 14:33) mmellon
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:

×93
×25
×22
×7

question asked: 30 Mar '17, 11:35

question was seen: 2,400 times

last updated: 31 Mar '17, 14:33