How to escape characters in DATEFORMAT pattern?

[Former Member]

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?

[VolkerBarth]

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

[Breck_Carter · Accepted solution]

Une technique de force brute, peut-être?

REPLACE(DATEFORMAT(current timestamp,'yy-MM-dd HH / nn'),'/','h')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
18-03-22 13 h 23 

[Former Member · Accepted solution]

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.

[Breck_Carter · Accepted solution]

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

[Former Member · Accepted solution]

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