tribuna33
asked on
Issue with date format while sending email via SMTP
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;
/
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;
/
what are you expecting and what are you getting?
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.
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.
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)
'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)
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
May be the mail server is using a different time zone to display the date/time in emails.
Thanks
ASKER
I found the solution - I commented date format and it works.
thanks to all for your help.
thanks to all for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.