0

How can I convert the string 'Fri, 9 Mar 2012 10:43:21 +0000 (UTC)', stored as a varchar(max), to the datetime value '09-03-2012', which is the date part of the string?

dsolimano
  • 8,870
  • 3
  • 48
  • 63
Vikram SE
  • 307
  • 2
  • 7
  • 23

4 Answers4

0

According to MS: http://msdn.microsoft.com/en-us/library/ms187928.aspx

The CONVERT() function is a general function that converts an expression of one data type to another. The CONVERT() function can be used to display date/time data in different formats.

and the table lists 105 as the code for the style you want.

So, for your example date:

CONVERT(VARCHAR(10), 'Fri, 9 Mar 2012 10:43:21 +0000 (UTC)', 105)

should probably do the trick.

andrewdotnich
  • 16,195
  • 7
  • 38
  • 57
0

Use convert and reference to this page http://msdn.microsoft.com/en-us/library/ms187928.aspx

e.g. CONVERT(varchar(8), GETDATE(), 112)

Nick
  • 1,128
  • 7
  • 12
0

I had to chop up your date first, but here goes:

DECLARE @ds varchar(30)='Fri, 9 Mar 2012 10:43:21 +0000 (UTC)';
SELECT CONVERT(VARCHAR(10)
,CAST(SUBSTRING(@ds
,CHARINDEX(' ',@ds)
,LEN(@ds)-(PATINDEX('%[0-9][0-9][0-9][0-9]%',@ds)+6))
AS DATETIME)
,105    
)

Result:

09-03-2012
John Dewey
  • 6,985
  • 3
  • 22
  • 26
0

If you have SQL Server 2012, you can try this:

select CONVERT(date, TRY_PARSE('Fri, 9 Mar 2012 10:43:21 +0000' AS datetime2))

NOTE: (UTC) is removed, it didn't parse correctly.

Also, be aware if you need to localize that time value. For example, I live in AZ, so typically I just subtract -7 hrs from UTC times to localize. You will need to make similar adjustments based on the time zone

TyT
  • 291
  • 2
  • 5