[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Issue with date format while sending email via SMTP

Posted on 2007-11-27
6
Medium Priority
?
2,444 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Technology Partners: 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!

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.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

650 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