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.5k5417241050
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:

×160
×7
×7

question asked: 22 Mar '18, 13:06

question was seen: 1,434 times

last updated: 22 Mar '18, 15:18