Re: How to set Date format in UTL_SMTP

On Mar 20, 7:46 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "aru" <arumugam..._at_gmail.com> a écrit dans le message de news: 1174389921.659772.72..._at_b75g2000hsg.googlegroups.com...
> | Hi,
> | I have one strange problem with UTL_SMTP.
> |
> | I am using the following code to set the date value and format in
> | header buffer:
> |
> | header:= 'Date: '||TO_CHAR(SYSDATE,'fmDy, DD Mon YYYY fxHH24:MI:SS')||
> | crlf||
> | 'From: '||sender||''||crlf||
> | 'Subject: '||subject||crlf||
> | 'To: '||recipient||crlf||
> | 'CC: '||ccrecipient ;
> |
> | With this method, the mails sent from database carry SYSDATE of the
> | machine where the Oracle Database is running. In my case, database is
> | running in India.
> |
> | People in US complain that they get future-dated mail. Since the
> | database is running in India and there is time difference of 12 hr
> | 30mins between the regions, this is expected. But when I send mail
> | using Java Mail API, I did not have this kind of issue. Anything can
> | be done for this???
> |
> | Help from somebody is highly appreciated.
> |
> | Thanks,
> | Ary
> |
>
> Use:
>
> to_char(systimestamp, 'Dy, dd Mon yy hh24:mi:ss tzhtzm')
>
> Then there is the time zone in your date.
>
> Regards
> Michel Cadot

To be on the completely safe side:

to_char(systimestamp, 'Dy, dd Mon yyyy hh24:mi:ss tzhtzm','NLS_DATE_LANGUAGE=American')

so that returned weekday and month names are in American/English regardless the NLS settings of the database and/or session. Not every Oracle db in the world runs in English... :) And to be completely compatible with RFC-2822, 4-digit year should be used.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Tue Mar 20 2007 - 16:03:36 CDT