Solved

Convert RFC822 format to date format in SQL 2008

Posted on 2013-06-27
22
847 Views
Last Modified: 2013-07-03
Can you please help me convert the RFC822 to date format in SQL 2008?
0
Comment
Question by:ramvbcsharp24
  • 8
  • 6
  • 3
  • +2
22 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39281667
Please provide some examples of the source data you wish to convert.  Which Date datatype do you want as your destination? date, datetime, datetimeoffset?
0
 

Author Comment

by:ramvbcsharp24
ID: 39281682
Hi,

Here is an RFC822 format:

Thu, 27 Jun 2013 15:11:05 UT


I would like to convert this to read 06/27/2013 11:15 AM

This should be in a date format in SQL Server 2008.

Please advise.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39281690
Does this do the trick?

select convert(varchar(25),getdate(),100)

Open in new window

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39281696
Based upon your comment...

select convert(varchar(25),getdate(),131)

Open in new window

0
 

Author Comment

by:ramvbcsharp24
ID: 39281715
Hi,

I tried using your suggestion but didn't get what I wanted:

SELECT convert( varchar(20), 'Mon Jun 03 18:41:35', 101)

Result:

Mon Jun 03 18:41:35

I need to get an output of 06/27/2013 11:20 AM
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39281716
Just playing with an idea.


declare @x nvarchar(100)
select @x = 'Thu, 27 Jun 2013 15:11:05 UT'

select cast(reverse(substring(reverse(substring(@x, 6, LEN(@x))), 4, LEN(@x))) as datetime)
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39281718
If you are selecting from a table...

select cast(reverse(substring(reverse(substring(myRFCDate, 6, LEN(myRFCDate))), 4, LEN(myRFCDate))) as datetime)
from myTable
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 39281722
Without building a string i dont think you can get it to specifically look like that. 131 comes close though... but it includes miliseconds.
0
 

Author Comment

by:ramvbcsharp24
ID: 39281745
Dear Isavidge,

Here's I tried so far:

Use mydtabase;
declare @x nvarchar(100)
select @x = 'LastManagedDateTime'

select cast(reverse(substring(reverse(substring(@x, 6, LEN(@x))), 4, LEN(@x))) as datetime)
From mytable

Note:
LastManagedDateTime is the RFC822 column from my table called mytable
The result of this run is:

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39281797
I would create a function that converts the RFC822 string to a datetime with logic similar to below though I would suggest including some error handling.

DECLARE @RFC822 varchar(50) = 'Thu, 27 Jun 2013 15:11:05 UT'
DECLARE @Output datetime

SET @RFC822 = SUBSTRING(@RFC822, CHARINDEX(',', @RFC822, 0) + 2, LEN(@RFC822))
SET @RFC822 = SUBSTRING(@RFC822, 0, LEN(@RFC822) - CHARINDEX(' ', REVERSE(@RFC822), 0) + 1)
SET @Output = CONVERT(DATETIME, @RFC822 + '.000', 113)
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39281909
The reason it fails is because you're trying to convert this 'LastManagedDateTime' into a datetime.

If you tell me the column name and table I'll write you the SQL. The vlaue in my example stored in @x is the RFC formatted date. It looks like you put a column name in there which won't work
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 500 total points
ID: 39282052
This (hopeful) solution deals with incoming strings in RFC822 date format that you wish to convert to datetime.

However, you'll still have the timezone to convert (EST, CST, PST, etc). For that you'll need a function.

Microsoft has one at http://social.msdn.microsoft.com/Forums/sqlserver/en-US/337b0ac4-2a35-4a42-ba08-6e84b18b38a0/convert-time-zone, but it doesn't work correctly. The embedded function (below) should do the trick.

The embedded snipped is the function. Below that is an example of using it.

This was worth the time, because I needed it as well and did not see a solution that encompassed daylight savings time, other than some which had to calculate the beginning and end of DLT each year, which was quite messy. Credit to lsavidge for the REVERSE function that parses the RFC822 date core value (in the example of usage at bottom).

CREATE FUNCTION udf_ConvertTime(
    @TimeToConvert  datetime,
    @TimeZoneFrom   varchar(5),
    @TimeZoneTo     varchar(5)
)
returns DateTime
as
begin
    declare @dtOutput   datetime,
            @nAdjust    smallint,
            @Local      smallint

    /* ------------------------------------------------------------------------ */
    /*  Important: If you want to convert to your local time, the following is  */
    /*  necessary to handle daylight savings time. Your SQLServer installation  */
    /*  must allow this function to execute xp_regread.                         */
    /* ------------------------------------------------------------------------ */

    SET @Local = CONVERT(smallint, datediff(hh, getutcdate(), getdate()) * 60)
    IF @TimeZoneTo = 'LOCAL'
    BEGIN
        DECLARE @root VARCHAR(32),
                @key  VARCHAR(128),
                @StandardBias VARBINARY(8),
                @DaylightBias VARBINARY(8)
        SET @root = 'HKEY_LOCAL_MACHINE'
        SET @key  = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
        EXEC master..xp_regread @root, @key, 'StandardBias', @StandardBias OUTPUT
        EXEC master..xp_regread @root, @key, 'DaylightBias', @DaylightBias OUTPUT
        IF @StandardBias <> @DayLightBias
            SET @Local = @Local - 60
    END

    /* ------------------------------------------------------------------------ */
    /*  Build a temporary table of timezone conversions.                        */
    /* ------------------------------------------------------------------------ */

    declare @temp table(
        TimeZone varchar(5),
        nOffset  smallint )

    insert into @Temp select 'A',60
    insert into @Temp select 'ACDT',630
    insert into @Temp select 'ACST',570
    insert into @Temp select 'ADT',-180
    insert into @Temp select 'AEDT',660
    insert into @Temp select 'AEST',600
    insert into @Temp select 'AKDT',-480
    insert into @Temp select 'AKST',-540
    insert into @Temp select 'AST',-240
    insert into @Temp select 'AWDT',540
    insert into @Temp select 'AWST',480
    insert into @Temp select 'B',120
    insert into @Temp select 'BST',60
    insert into @Temp select 'C',180
    insert into @Temp select 'CDT',-300
    insert into @Temp select 'CEDT',120
    insert into @Temp select 'CEST',120
    insert into @Temp select 'CET',60
    insert into @Temp select 'CST',-360
    insert into @Temp select 'CXT',420
    insert into @Temp select 'D',240
    insert into @Temp select 'E',300
    insert into @Temp select 'EDT',-240
    insert into @Temp select 'EEDT',180
    insert into @Temp select 'EEST',180
    insert into @Temp select 'EET',120
    insert into @Temp select 'EST',-300
    insert into @Temp select 'F',360
    insert into @Temp select 'G',420
    insert into @Temp select 'GMT',0
    insert into @Temp select 'H',480
    insert into @Temp select 'HAA',-180
    insert into @Temp select 'HAC',-300
    insert into @Temp select 'HADT',-540
    insert into @Temp select 'HAE',-240
    insert into @Temp select 'HAP',-420
    insert into @Temp select 'HAR',-360
    insert into @Temp select 'HAST',-600
    insert into @Temp select 'HAT',-150
    insert into @Temp select 'HAY',-480
    insert into @Temp select 'HNA',-240
    insert into @Temp select 'HNC',-360
    insert into @Temp select 'HNE',-300
    insert into @Temp select 'HNP',-480
    insert into @Temp select 'HNR',-420
    insert into @Temp select 'HNT',-210
    insert into @Temp select 'HNY',-540
    insert into @Temp select 'I',540
    insert into @Temp select 'IST',60
    insert into @Temp select 'K',600
    insert into @Temp select 'L',660
    insert into @temp select 'LOCAL', @Local
    insert into @Temp select 'M',720
    insert into @Temp select 'MDT',-360
    insert into @Temp select 'MESZ',120
    insert into @Temp select 'MEZ',60
    insert into @Temp select 'MST',-420
    insert into @Temp select 'N',-60
    insert into @Temp select 'NDT',-150
    insert into @Temp select 'NFT',690
    insert into @Temp select 'NST',-210
    insert into @Temp select 'O',-120
    insert into @Temp select 'P',-180
    insert into @Temp select 'PDT',-420
    insert into @Temp select 'PST',-480
    insert into @Temp select 'Q',-240
    insert into @Temp select 'R',-300
    insert into @Temp select 'S',-360
    insert into @Temp select 'T',-420
    insert into @Temp select 'U',-480
    insert into @Temp select 'UTC',0
    insert into @Temp select 'V',-540
    insert into @Temp select 'W',-600
    insert into @Temp select 'WEDT',60
    insert into @Temp select 'WEST',60
    insert into @Temp select 'WET',0
    insert into @Temp select 'WST',540
    insert into @Temp select 'WST',480
    insert into @Temp select 'X',-660
    insert into @Temp select 'Y',-720
    insert into @Temp select 'Z',0

    /* ------------------------------------------------------------------------ */
    /*  Return the difference between the from/to timezones.                    */
    /* ------------------------------------------------------------------------ */

    select @nAdjust = nOffset
    from   @Temp
    where  timeZone = @TimeZoneFrom

    select @nAdjust = nOffset - @nAdjust
    from   @Temp
    where  timeZone = @TimeZoneTo

    set @dtOutput = dateadd(n,@nAdjust,@TimeToConvert)
    return @dtOutput
end

Open in new window

Example of usage:
DECLARE @RFC822Date varchar(32),
        @MyDate     datetime,
        @FromTZ     varchar(4)

SET @RFC822Date = 'Wed, 02 Oct 2002 08:00:00 EST'
SET @FromTZ     = RTRIM(LTRIM(RIGHT(@RFC822Date, 4))) -- in this case, EST
SET @MyDate     = CONVERT(datetime, REVERSE(SUBSTRING(REVERSE(SUBSTRING(@RFC822Date, 6, LEN(@RFC822Date))), 4, LEN(@RFC822Date))))

SELECT  @MyDate         AS MyDate,
        @RFC822Date     AS RFCDate,
        dbo.udf_ConvertTime(@MyDate, @FromTZ, 'CST') AS [to]

SELECT  @MyDate         AS MyDate,
        @RFC822Date     AS RFCDate,
        dbo.udf_ConvertTime(@MyDate, @FromTZ, 'LOCAL') AS [to]

Open in new window

0
 

Author Comment

by:ramvbcsharp24
ID: 39282311
This is great! Can this function be used to convert the entire column having an RFC822 format to the date format in SQL Server 2008?
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39282378
Yes. I created a @TestTable to demonstrate. The joined subquery parses the RFC822 date, then the subsequent function converts it to local time (including if on daylight savings time). Two birds with one stone. :)

DECLARE @TestTable TABLE (
    Seq         int         NOT NULL IDENTITY(1, 1),
    RFC822Date  varchar(32),
    MyDate      datetime    )

INSERT INTO @TestTable (RFC822Date)
SELECT 'Wed, 02 Oct 2002 08:00:00 EST' UNION ALL
SELECT 'Thu, 19 Jul 2007 21:51:27 BST' UNION ALL
SELECT 'Wed, 16 Dec 2009 15:18:11 PDT'

UPDATE  @TestTable
SET     MyDate = dbo.udf_ConvertTime(t1.StandardDate, RTRIM(LTRIM(RIGHT(t1.RFC822Date, 4))), 'LOCAL')
FROM  ( SELECT  Seq AS MySeq,
        RFC822Date,
        CONVERT(datetime, REVERSE(SUBSTRING(REVERSE(SUBSTRING(RFC822Date, 6, LEN(RFC822Date))), 4, LEN(RFC822Date)))) AS StandardDate
FROM    @TestTable ) t1
WHERE   Seq = MySeq

SELECT * FROM @TestTable

Open in new window

I created a blog entry about this. Has come in quite useful for me, too.
0
 

Author Comment

by:ramvbcsharp24
ID: 39282561
Hi,

Sorry about this but I just would like to use a simple conversion without using a function in this statement:

Use mydb;
SELECT CONVERT(datetime, REVERSE(SUBSTRING(REVERSE(SUBSTRING(LastManagedDateTime, 6, LEN(LastManagedDateTime))), 4, LEN(LastManagedDateTime)))) AS StandardDate
FROM   mytable

Still I have this error:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39282574
Can you post some of the values that are in your table? Perhaps your data has the +5:00, -5:00, +3:00, -3:00 types of RFC822 extensions on the dates, rather than EST, CST, etc, ones.
0
 

Author Comment

by:ramvbcsharp24
ID: 39282581
Sure,

Here is my sample dates:

select CONVERT(datetime, REVERSE(SUBSTRING(REVERSE(SUBSTRING('Mon Jun 03 18:41:35', 6, LEN('Mon Jun 03 18:41:35'))), 4, LEN('Mon Jun 03 18:41:35'))))
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39282660
The standard for RFC822 dates includes the comma. Your data does not. So, to accommodate the REVERSE parsing WITHOUT a comma, change "6" to "5", if you know you'll always NOT have a comma in your string dates.

One of us probably could have caught the discrepancy between your 2nd and 3rd posts, where one had the comma and the latter did not, but no one did. Consistency, thou are a jewel.

You probably don't really need the udf_ConvertTime function, since you have no offsets at the end of your date (i.e., EST, +5.00, etc), so the REVERSE parsing will probably get you to your objective.
0
 

Author Comment

by:ramvbcsharp24
ID: 39282714
Sorry about that. I tried changing the REVERSE parsing to 5 since I don't use comma in my string dates. So far no luck.
I am attaching an excel file similar to the table in SQL Server 2008 I am using.
In SQL Server 2008, the Network Function is a string column while the LastManagedDateTime is RFC822 format.

So maybe you can help me out of my dilemma.
Book1.xls
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39282810
You don't even have the year in them. This gets better. Get the year onto your data, and your world will get a lot better.
0
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 39282877
Well, I may have the catch-all function that will do the trick. First compile this function in your database, then run the code that follows. You should be able to figure out how to adapt the example code to your needs. It works with or without the year in the (really, these are not) RFC822 dates.

CREATE FUNCTION udf_ConvertTime(
    @TimeToConvert  varchar(80),
    @TimeZoneFrom   varchar(8),
    @TimeZoneTo     varchar(8)
)
RETURNS DateTime
AS
BEGIN
    DECLARE @dtOutput   datetime,
            @nAdjust    smallint,
            @Local      smallint,
            @FromDate   datetime,
            @RFC822     tinyint,
            @WkTime     varchar(80),
            @Start      tinyint

    SET @RFC822 = 0

    /* ------------------------------------------------------------------------ */
    /*  We are providing a varchar(80) date field to facilitate RFC822 dates.   */
    /* ------------------------------------------------------------------------ */

    IF ISDATE(@TimeToConvert) = 1
        SET @FromDate = CONVERT(datetime, @TimeToConvert)
    ELSE
    BEGIN
        SET @Start  = 1
        IF CHARINDEX(' ', RIGHT(@TimeToConvert, 7)) > 0
            SET @Start = CHARINDEX(' ', REVERSE(RIGHT(@TimeToConvert, 7))) + 1

        SET @WkTime = REPLACE(@TimeToConvert, ',', '')
        SET @WkTime = REVERSE(
                        SUBSTRING(
                            REVERSE(
                                SUBSTRING(@WkTime, 5, LEN(@WkTime))
                                   ), @Start, LEN(@WkTime)))

        IF CHARINDEX(' ', @WkTime)     = 4  AND
           CHARINDEX(' ', @WkTime, 5)  = 7  AND
           CHARINDEX(':', @WkTime, 8)  = 10 AND
           CHARINDEX(':', @WkTime, 11) = 13 -- Means we have no year
            SET @WkTime = LEFT(@WkTime, 7) + CONVERT(varchar(5), YEAR(GETDATE())) + SUBSTRING(@WkTime, 7, 40)

        IF ISDATE(@WkTime) = 1
        BEGIN
            SET @FromDate = CONVERT(datetime, @WkTime)
            SET @RFC822   = 1
        END
    END

    IF @FromDate IS NULL
        RETURN @FromDate

    /* ------------------------------------------------------------------------ */
    /*  If this is an RFC822 date, and if we have no "from" zone, supply it.    */
    /* ------------------------------------------------------------------------ */

    IF @RFC822 = 1 AND @TimeZoneFrom IS NULL
    BEGIN
        SET @TimeZoneFrom = 'LOCAL'
        IF CHARINDEX(' ', RIGHT(@TimeToConvert, 7)) > 0
        BEGIN
            SET @TimeZoneFrom = RIGHT(@TimeToConvert, 7)
            SET @TimeZoneFrom = SUBSTRING(@TimeZoneFrom, CHARINDEX(' ', RIGHT(@TimeToConvert, 7)), 7)
            SET @TimeZoneFrom = RTRIM(LTRIM(@TimeZoneFrom))
            IF ISNUMERIC(@TimeZoneFrom) = 1
                SET @nAdjust = CONVERT(smallint, CONVERT(smallint, @TimeZoneFrom) * 60 / 1000)
            IF ISNUMERIC(@TimeZoneFrom) = 0 AND CHARINDEX(':', @TimeZoneFrom) > 0
                SET @nAdjust = CONVERT(smallint, SUBSTRING(@TimeZoneFrom, 1, CHARINDEX(':', @TimeZoneFrom) - 1)) * 60
        END
    END

    /* ------------------------------------------------------------------------ */
    /*  If the from and to are the same, we need go no further.                 */
    /* ------------------------------------------------------------------------ */

    IF @TimeZoneFrom = @TimeZoneTo
        RETURN @FromDate

    /* ------------------------------------------------------------------------ */
    /*  Important: If you want to convert to your local time, the following is  */
    /*  necessary to handle daylight savings time. Your SQLServer installation  */
    /*  must allow this function to execute xp_regread.                         */
    /* ------------------------------------------------------------------------ */

    SET @Local = CONVERT(smallint, datediff(hh, getutcdate(), getdate()) * 60)
    IF @TimeZoneTo = 'LOCAL'
    BEGIN
        DECLARE @root VARCHAR(32),
                @key  VARCHAR(128),
                @StandardBias VARBINARY(8),
                @DaylightBias VARBINARY(8)
        SET @root = 'HKEY_LOCAL_MACHINE'
        SET @key  = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
        EXEC master..xp_regread @root, @key, 'StandardBias', @StandardBias OUTPUT
        EXEC master..xp_regread @root, @key, 'DaylightBias', @DaylightBias OUTPUT
        IF @StandardBias <> @DayLightBias
            SET @Local = @Local - 60
    END

    /* ------------------------------------------------------------------------ */
    /*  Build a temporary table of timezone conversions.                        */
    /* ------------------------------------------------------------------------ */

    declare @temp table(
        TimeZone varchar(8),
        nOffset  smallint )

    insert into @Temp select 'A',60
    insert into @Temp select 'ACDT',630
    insert into @Temp select 'ACST',570
    insert into @Temp select 'ADT',-180
    insert into @Temp select 'AEDT',660
    insert into @Temp select 'AEST',600
    insert into @Temp select 'AKDT',-480
    insert into @Temp select 'AKST',-540
    insert into @Temp select 'AST',-240
    insert into @Temp select 'AWDT',540
    insert into @Temp select 'AWST',480
    insert into @Temp select 'B',120
    insert into @Temp select 'BST',60
    insert into @Temp select 'C',180
    insert into @Temp select 'CDT',-300
    insert into @Temp select 'CEDT',120
    insert into @Temp select 'CEST',120
    insert into @Temp select 'CET',60
    insert into @Temp select 'CST',-360
    insert into @Temp select 'CXT',420
    insert into @Temp select 'D',240
    insert into @Temp select 'E',300
    insert into @Temp select 'EDT',-240
    insert into @Temp select 'EEDT',180
    insert into @Temp select 'EEST',180
    insert into @Temp select 'EET',120
    insert into @Temp select 'EST',-300
    insert into @Temp select 'F',360
    insert into @Temp select 'G',420
    insert into @Temp select 'GMT',0
    insert into @Temp select 'H',480
    insert into @Temp select 'HAA',-180
    insert into @Temp select 'HAC',-300
    insert into @Temp select 'HADT',-540
    insert into @Temp select 'HAE',-240
    insert into @Temp select 'HAP',-420
    insert into @Temp select 'HAR',-360
    insert into @Temp select 'HAST',-600
    insert into @Temp select 'HAT',-150
    insert into @Temp select 'HAY',-480
    insert into @Temp select 'HNA',-240
    insert into @Temp select 'HNC',-360
    insert into @Temp select 'HNE',-300
    insert into @Temp select 'HNP',-480
    insert into @Temp select 'HNR',-420
    insert into @Temp select 'HNT',-210
    insert into @Temp select 'HNY',-540
    insert into @Temp select 'I',540
    insert into @Temp select 'IST',60
    insert into @Temp select 'K',600
    insert into @Temp select 'L',660
    insert into @temp select 'LOCAL', @Local
    insert into @Temp select 'M',720
    insert into @Temp select 'MDT',-360
    insert into @Temp select 'MESZ',120
    insert into @Temp select 'MEZ',60
    insert into @Temp select 'MST',-420
    insert into @Temp select 'N',-60
    insert into @Temp select 'NDT',-150
    insert into @Temp select 'NFT',690
    insert into @Temp select 'NST',-210
    insert into @Temp select 'O',-120
    insert into @Temp select 'P',-180
    insert into @Temp select 'PDT',-420
    insert into @Temp select 'PST',-480
    insert into @Temp select 'Q',-240
    insert into @Temp select 'R',-300
    insert into @Temp select 'S',-360
    insert into @Temp select 'T',-420
    insert into @Temp select 'U',-480
    insert into @Temp select 'UTC',0
    insert into @Temp select 'V',-540
    insert into @Temp select 'W',-600
    insert into @Temp select 'WEDT',60
    insert into @Temp select 'WEST',60
    insert into @Temp select 'WET',0
    insert into @Temp select 'WST',540
    insert into @Temp select 'WST',480
    insert into @Temp select 'X',-660
    insert into @Temp select 'Y',-720
    insert into @Temp select 'Z',0

    /* ------------------------------------------------------------------------ */
    /*  Return the difference between the from/to timezones.                    */
    /* ------------------------------------------------------------------------ */

    IF @nAdjust IS NULL
        select @nAdjust = nOffset
        from   @Temp
        where  timeZone = @TimeZoneFrom

    select @nAdjust = nOffset - @nAdjust
    from   @Temp
    where  timeZone = @TimeZoneTo

    set @dtOutput = dateadd(n,@nAdjust,@FromDate)
    return @dtOutput
END

Open in new window

DECLARE @TestTable TABLE (
    Seq         int         NOT NULL IDENTITY(1, 1),
    RFC822Date  varchar(32),
    MyDate      datetime    )

INSERT INTO @TestTable (RFC822Date)
SELECT 'Wed, 02 Oct 2002 08:00:00 EST' UNION ALL
SELECT 'Thu, 19 Jul 2007 21:51:27 BST' UNION ALL
SELECT 'Wed, 16 Dec 2009 15:18:11 PDT' UNION ALL
SELECT 'Mon Jun 03 18:41:35' UNION ALL
SELECT 'Mon May 20 21:47:39' UNION ALL
SELECT 'Tue Jun 04 06:37:23' UNION ALL
SELECT 'Mon Jun 03 16:00:02' UNION ALL
SELECT 'Tue Jun 04 00:23:45' UNION ALL
SELECT 'Tue Jun 04 04:47:02'

UPDATE  @TestTable
SET     MyDate = dbo.udf_ConvertTime(RFC822Date, NULL, 'LOCAL')

SELECT * FROM @TestTable

Open in new window

0
 

Author Closing Comment

by:ramvbcsharp24
ID: 39297863
Thank you very much, I tried dsacker's solution and works perfectly well.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now