Question Re: dateformat (...) to regional date format in Technology Q&A

dateformat (...) to regional date format

dhkom — Wed, 14 Sep 2022 11:36:55 GMT

How do I convert a date into a string formatted per the computer's regional date setting? I only see how to specify explicit formats. For example, in PowerBuilder, I would specify date format string "[shortdate]". Forgive me if I'm missing something simple. Thanks!

Re: dateformat (...) to regional date format

johnsmirnios — Wed, 14 Sep 2022 12:05:42 GMT

The server does not automatically format dates according to the client connection's local OS settings. Generally, that is left up to the client app which can format the date itself or have the server do it using explicit formats via dateformat arguments or the DATE_FORMAT option (https://dcx.sap.com/index.html#sqla170/en/html/813a8b556ce21014958ea27cd4f0b21c.html*loio813a8b556ce21014958ea27cd4f0b21c)

Re: dateformat (...) to regional date format

VolkerBarth — Thu, 15 Sep 2022 04:21:21 GMT

If you really need to format dates within the server (say, because there is no client app in-place) and you are using Windows as platform, you could take the (somewhat difficult) route to use a native function calling the GetDateFormatEx() WinAPI or similar functions...

Here's an article from Breck's blog with a sample using the GetOpenFileName() WinAPI - just to show the general approach.

Re: dateformat (...) to regional date format

johnsmirnios — Thu, 15 Sep 2022 04:32:14 GMT

Note that this approach would give a date in the format & language of the OS where the database server is running rather than the client. Each client should follow its own rules.

Re: dateformat (...) to regional date format

VolkerBarth — Thu, 15 Sep 2022 04:43:35 GMT

Thanks for pointing this out - I had thought the server's location would actually be meant with "the computer's regional..." - thinking of a local engine.

Re: dateformat (...) to regional date format

johnsmirnios — Thu, 15 Sep 2022 08:22:55 GMT

That's true. If it is a local engine on the desktop, the regional settings would match. If it is running as a service or as another user, the settings still may not match even if it is on the same machine.

Re: dateformat (...) to regional date format

VolkerBarth — Thu, 15 Sep 2022 08:32:17 GMT

That would depend on the used "lpLocaleName" entry within the API call, methinks.

Re: dateformat (...) to regional date format

dhkom — Thu, 15 Sep 2022 09:08:40 GMT

Thanks, all. You bring up good points on the complexities. In this case, the server is Windows, and the client is a web application running in the Browser. The GetDateFormatEx() solution on the server will work out now, but maybe not in the future. Perhaps an application setting should define the date display format in this case? I'll give this some thought.

Re: dateformat (...) to regional date format

VolkerBarth — Thu, 15 Sep 2022 09:41:05 GMT

Just to clarify: You can also set options like date_order/date_format/timestamp_format per connection and can use dateformat() with the according connection properties to provide connection-specific formatting, so possibly a client app could also provide regional settings to the server via those options...