?
Solved

Issue with date format while sending email via SMTP

Posted on 2007-11-27
6
Medium Priority
?
2,404 Views
Last Modified: 2013-12-18
I have issue to format date using Oracle SMTP package. I tried several date formats, but none is working.
When MS email is opened the date format in "Sent:" always shows the incorrect date/time:

Following is the example:

CREATE OR REPLACE PROCEDURE send_email (
   subjecttext     IN       VARCHAR2,
   email_address   IN       VARCHAR2,
   message_body    IN       LONG
)
IS
   sendoraddress   VARCHAR2 (30)       := ' sender@email.com';
   emailserver     VARCHAR2 (30)       := 'smtp.address.com';
   port            NUMBER              := 25;
   conn            UTL_SMTP.connection;
   mesg_header     VARCHAR2 (4000);
   subject         VARCHAR2 (200);
   crlf            VARCHAR2 (2)        := CHR (13) || CHR (10);
BEGIN
   subject := subjecttext;
   conn := UTL_SMTP.open_connection (emailserver, port);
   UTL_SMTP.helo (conn, emailserver);
   UTL_SMTP.mail (conn, sendoraddress);
   UTL_SMTP.rcpt (conn, email_address);

   mesg_header :=
            'Date: '
         || TO_CHAR (SYSDATE, 'MM DD YYYY HH:MI:SS')
         || crlf
         || 'From:'
         || sendoraddress
         || crlf
         || 'Subject:'
         || subject
         || crlf
         || 'To: '
         || email_address
         || crlf
         || ''
         || crlf;
   END IF;

   UTL_SMTP.DATA (conn, mesg_header || crlf || crlf || message_body);
   UTL_SMTP.quit (conn);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (   'ERROR SQLCODE '
                            || SQLCODE
                            || ' '
                            || SUBSTR (SQLERRM, 1, 200)
                           );
END;
/
0
Comment
Question by:tribuna33
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 20361772
what are you expecting and what are you getting?
0
 
LVL 1

Expert Comment

by:michaeljoneill
ID: 20362095
Please define "not working" more precisely.  

Regardless, I would try HH24 instead of HH in your cast: TO_CHAR (SYSDATE, 'MM DD YYYY HH24:MI:SS') , and I would use utl_tcp.crlf instead of your own private variable crlf.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20362723
according to RFC 822,  your date/time format must be ....


'dd Mon yy hh24:mi'

I derived this from section 5.1 of the rfc



date-time   =  [ day "," ] date time        ; dd mm yy
                                                 ;  hh:mm:ss zzz

     day         =  "Mon"  / "Tue" /  "Wed"  / "Thu"
                 /  "Fri"  / "Sat" /  "Sun"

     date        =  1*2DIGIT month 2DIGIT        ; day month year
                                                 ;  e.g. 20 Jun 82

     month       =  "Jan"  /  "Feb" /  "Mar"  /  "Apr"
                 /  "May"  /  "Jun" /  "Jul"  /  "Aug"
                 /  "Sep"  /  "Oct" /  "Nov"  /  "Dec"

     time        =  hour zone                    ; ANSI and Military

     hour        =  2DIGIT ":" 2DIGIT [":" 2DIGIT]
                                                 ; 00:00:00 - 23:59:59

     zone        =  "UT"  / "GMT"                ; Universal Time
                                                 ; North American : UT
                 /  "EST" / "EDT"                ;  Eastern:  - 5/ - 4
                 /  "CST" / "CDT"                ;  Central:  - 6/ - 5
                 /  "MST" / "MDT"                ;  Mountain: - 7/ - 6
                 /  "PST" / "PDT"                ;  Pacific:  - 8/ - 7
                 /  1ALPHA                       ; Military: Z = UT;
                                                 ;  A:-1; (J not used)
                                                 ;  M:-12; N:+1; Y:+12
                 / ( ("+" / "-") 4DIGIT )        ; Local differential
                                                 ;  hours+min. (HHMM)


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20363291
where is your email server hosted ? it is in the same location where you are running the code from or not ?

May be the mail server is using a different time zone to display the date/time in emails.

Thanks
0
 

Author Comment

by:tribuna33
ID: 20367893
I found the solution - I commented date format and it works.
thanks to all for your help.
 
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 375 total points
ID: 20368009
of course commenting to remove it completely works, all of the header fields are optional but you should be able to format and use whatever date you want too.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question