Re: Date formatting with Timezone

2016-11-02 15:43 GMT+00:00 Marco Baurdoux <[email protected]>:
> Hello,
>
>
>
> We're running Freetds 1.00.15 on a CentOS Linux release 7.2.1511 and
> connecting to MS-SQL 2014
>
>
>
> We're trying to obtain the date in the following format "2016-11-02 13 :28
> :32.0000000 +02:00". For this we've configured the date format parameter in
> the locales.conf file to %Y-%m-%d %H:%M:%S.%z %Z
>
> Depending on which tdsver we configure, even the output of "%Y-%m-%d
> %H:%M:%S.%z %Z" is different:
>
> -          With TDSVER 7.1 or 7.2 we obtain a date formatted like
> "2016-11-02 13:28:32.0000000 +00:00". Exactly what we want.
>
> -          With TDSVER 7.3  or 7.4 we obtain a date formatted like
> "2016-11-02 13:28:32.0000000 CET. This breaks our PHP API but it respects
> the strftime description of %Z. I'm unable to find how to obtain the time
> zone in "+00:00" format.
>
>
>
> Is there a solution to work with the latest version of Freetds and still get
> the timezone is the wished format or are we doomed (:)) to stick to version
> 7.2?
>
>
>
> Thank you for any feedback/advice.
>
>
>
> Regards,
>
>
>
> Marco Baurdoux
>

I think you are using a DATETIMEOFFSET type. This type started to be
supported since TDS 7.3 (sql 2008)
if you connect with a previous TDS version the server is just sending
back varchar/nvarchar (I don't remember
exactly) so the "2016-11-02 13:28:32.0000000 +00:00" is formatted from
the server, not by the client and
format string in locales.conf do not apply. Note that the most similar
formatting (+/-HHMM) you can get with
a standard strftime using %z however we used %z as a fractional
decimal specifier. So looks like was a mistake
to use %z as new modifier.

Note also the %Z on Windows behave like %z
(http://php.net/manual/en/function.strftime.php) but
probably you are not using Windows :-)

Looking at new man pages for strftime looks like you can specify E or
O modifier (like %Ec) to have alternate
formats. Perhaps would be a good idea to use some different way to
specify fractional seconds (%OOS ??)
and another to specify +/-HH:MM (%OOz ??) this these looks like new extensions.

See http://pubs.opengroup.org/onlinepubs/9699919799/functions/strftime.html

Frediano