Thanks for clarifying Thomas, reading your message I can now see it clearly that COALESCE() returns a string which I then type cast to TIMESTAMP from them on the logic to handle dates in regional settings takes effect and I get my expected value.

Kind regards
Bhavbhuti

 

Hi Bhavbhuti,

I do not know the exact solution, but I'd say:

> COALESCE(CAST(tPB.dBillDt AS TIMESTAMP), '') AS tDocDt

converts timestamp inside servers environment to a string - the
returning client receives a string and does not know about it's datatype
- yet does not a apply specific translations.

With

> CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt

it's just the other way round: While the server deals with string and
timestamp, the client receives a timestamp an does know what to with it.

I'd say, this behavior is expectable...

Best regards, Thomas

Am 10.05.2014 15:21, schrieb venussoftop@... [firebird-support]:
>
>
> Hi all
>
> I have the following in my SELECT statement
>
> COALESCE(CAST(tPB.dBillDt AS TIMESTAMP), '') AS tDocDt
>
> The COALESCE() converts the CAST()ed TIMESTAMP to ANSI standard
> 2014-05-03 00:00:00.0000
>
>
>
> CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt
>
> The CAST() converts the COALESCE()ed TIMESTAMP to my regional and
> expected format 03.05.2014, 00:00:00.0000
>
>
> Is this an expected behavior? Please advise.
>
> Thanks and regards
> Bhavbhuti

--
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker

Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckmann@... <mailto:thomas.beckmann@...>

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
info@... <mailto:info@...> • www.assfinet.de
<http://www.assfinet.de/>

Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.

-- 


Thanking you.

Yours Faithfully,
For Venus Software Operations
----
Mr. Bhavbhuti Nathwani
___________________________________________
Softwares for Indian Businesses at: http://www.venussoftop.com

venussoftop@...
venussoftop@...
___________________________________________

Please note: We reserve complete rights for policy changes in the future and the same will be applicable immediately as and when made.  Attachments may get corrupted before reaching you, in such a situation please let us know and we will resend you the same at the earliest.  We do not take any responsibility for data loss of any type and kind.  Data safety remains the sole the responsibility of the users of our softwares.
___________________________________________

Internet email confidentiality:

This message may contain information that may be privileged or confidential.  If you are not the addressee nor are you responsible for the delivery of the message to the addressee indicated in this email, then you may not copy or deliver this email to anyone and you should notify the sender by reply email and then destroy this message.

Please reply email immediately to this message with REMOVE in the subject, if you or your employer do not consent to email of this kind.

Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by my company.