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.

Hello everyone,

In French (Canada) locale, the pattern for a date is something like "yy-MM-dd HH 'h' nn", where the 'h' here means h, and not a pattern part (as instance, "18-03-22 14 h 01). The default escape for cases like that (in Android, for example) is the single quote symbol, but it does not seem to work for Ultralite.

Is there a way to escape symbols in DATEFORMAT call?

asked 22 Mar '18, 13:06

Alex's gravatar image

Alex
1.1k274756
accept rate: 25%

Is there a way to escape symbols in DATEFORMAT call?

Well, I was sure you would escape those with a backslash (like with control characters, so here as 'yy-MM-dd HH \h nn)' but that does not work, either, the backslash appears as-is, and the 'h' is still treated as a date part...

(22 Mar '18, 15:18) Volker Barth

Une technique de force brute, peut-être?

REPLACE(DATEFORMAT(current timestamp,'yy-MM-dd HH / nn'),'/','h')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

18-03-22 13 h 23

permanent link

answered 22 Mar '18, 13:24

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 22 Mar '18, 13:25

Thank you @Breck Carter! It did work, although this pattern is a little bit flexible, and I can have a lot of other formats which may led to the same problem. It would be nice if the ' symbol escaped the character, as it already happens with FORMAT SQL Server macro and the default date/time pattern escaping in Android and iOS frameworks.

(22 Mar '18, 13:31) Alex
Replies hidden
2

Historically, there has been some vendor resistance to adding client-side formatting features to SQL... and it's now probably safe to say it's not ever going to happen.

However, the alternative is fun: Write your own CREATE FUNCTION supérieurDATEFORMAT ( in_timestamp TIMESTAMP, in_pattern VARCHAR ( 100 ) ) RETURNS VARCHAR ( 100 ).

(22 Mar '18, 13:50) Breck Carter

Unfortunately Ultralite does not provide functions, however, I did manage this scenario in the Android layer, using the approach that you suggested! Thank you =)

(22 Mar '18, 14:58) Alex
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:

×162
×7
×7

question asked: 22 Mar '18, 13:06

question was seen: 1,657 times

last updated: 22 Mar '18, 15:18