Question Re: How to escape characters in DATEFORMAT pattern? in Technology Q&A

How to escape characters in DATEFORMAT pattern?

Former Member — Thu, 22 Mar 2018 13:06:08 GMT

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?

Re: How to escape characters in DATEFORMAT pattern?

Breck_Carter — Thu, 22 Mar 2018 13:24:55 GMT

Une technique de force brute, peut-être?

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

18-03-22 13 h 23

Re: How to escape characters in DATEFORMAT pattern?

Former Member — Thu, 22 Mar 2018 13:31:26 GMT

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.

Re: How to escape characters in DATEFORMAT pattern?

Breck_Carter — Thu, 22 Mar 2018 13:50:54 GMT

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

Re: How to escape characters in DATEFORMAT pattern?

Former Member — Thu, 22 Mar 2018 14:58:45 GMT

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

Re: How to escape characters in DATEFORMAT pattern?

VolkerBarth — Thu, 22 Mar 2018 15:18:39 GMT

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