SQL Server

Get the Email Received Date/Time

See more Email Object Examples

Get's the date/time from the topmost Received header. The date/time of when you received an email may be different than the date/time stored in the Date header field, which if truthful, is the date when the email was sent.

The Received header field will look something like this:

Received: from mail.example.com (mail.example.com [99.255.255.99])
 by inbound-smtp.us-west-2.amazonaws.com with SMTP id 72ma443vs1g0o6vqd8erojkpss35s0dt32h323o1
 for admin@chilkatsoft.com;
 Wed, 25 Jul 2018 08:04:23 +0000 (UTC)

The date/time is the final part delimited by a semicolon. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/p.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    EXEC sp_OAMethod @email, 'GetHeaderField', @sTmp0 OUT, 'Received'
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @sTmp0

    -- Replace semicolons with CRLF's
    DECLARE @numReplaced int
    EXEC sp_OAMethod @sb, 'Replace', @numReplaced OUT, ';', CHAR(13) + CHAR(10)

    DECLARE @st int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @st OUT

    EXEC sp_OAMethod @st, 'AppendFromSb', @success OUT, @sb

    EXEC sp_OAGetProperty @st, 'Count', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'Should have at least one line..'
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @st
        RETURN
      END

    -- The date/time string is the last line in the string table.
    EXEC sp_OAGetProperty @st, 'Count', @iTmp0 OUT
    EXEC sp_OAMethod @st, 'StringAt', @sTmp0 OUT, @iTmp0 - 1
    EXEC sp_OAMethod @sb, 'SetString', @success OUT, @sTmp0
    EXEC sp_OAMethod @sb, 'Trim', @success OUT


    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT 'Received date/time = ' + @sTmp0

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @st


END
GO