SQL Server Performance Forum – Threads Archive

Best way to convert RSS pubDate to DATETIME?

Hi, I’m developing a RSS news reader to company website and would like to archive some RSS news to database. Everything works fine, but I would like to use SQL Server datetime format instead of varchar for the pubDate column. So what would be the best way to convert date string (see examples below) to SQL Server 2000 datetime format? Even better if they were converted to database server sysdate with respect to different time zones. Here are some possible cases:
Wed, 18 Jan 2006 12:36:17 CDT
Wed, 18 Jan 2006 14:27:38 GMT
Wed, 18 Jan 2006 16:00:00 EST BR,
Mika

One of the methods Select data as varcharDate, convert(datetime,substring(data,6,20)) as Date_Time from
(
select ‘Wed, 18 Jan 2006 12:36:17 CDT’ as data union all
select ‘Wed, 18 Jan 2006 14:27:38 GMT’ union all
select ‘Wed, 18 Jan 2006 16:00:00 EST’
) T
Madhivanan Failing to plan is Planning to fail

Thanks for prompt reply. Yeah, I could take a part of the string and convert it to datetime. Does this mean SQL Server has no understanding of time zones whatsoever? I can however make the conversion (to Greenwich Mean Time, for example) in server side pretty easily before saving news to database. Too bad there are so many regional time zone abbreviations (GMT, BST, IST, WET…) available I should play with. Well, maybe there’s a ready function for that purpose. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> -Mika

GETUTCDATE() will give you the UTC date of the server. You need to calculate the offset in the client. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>