Wednesday, April 23, 2008

Time zone and Header in Emails using utl_smtp

One of the problems I have had for what seems like ages is the incorrect time sent while using utl_smtp, or maybe I should say how I was using it. Everytime I would send an Email in OWB or any Stored Procedure, it would show a five hour difference (yes I live in the central time zone here in the USA). Now I knew it was something about the time zone, but for the life of me I just didn’t have the time to figure it out. Below is an example of what my Inbox said I got it versus when it was sent. (figure 1)

What I was saying earlier was how I was using it. When I created the message header, it looked like this.

-- Build the envelope
'Date: 'TO_CHAR( v_SystemDate, 'dd Mon yy hh24:mi:ss' ) crlf
'From: ' p_From_Alias crlf
'Subject: ' p_Subject crlf 'To: ';

Notice the TO_CHAR( v_SystemDate, 'dd Mon yy hh24:mi:ss' ). In the world of email, this is actually incorrect. It does not take into affects of time zones. If you use the SYSTIMESTAMP with the correct format, then your emails will show correctly. Here is an example of how to format the time



No comments: