Solved

Issue with date format while sending email via SMTP

Posted on 2007-11-27
6
2,324 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 125 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

861 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