Convert String to date and get Datepart

Question

I have a string column which has datetime values in this format (Tue Nov 8 06:14:57 GMT 2016). How can I get the date part of this values? like.

SELECT DATEPART(MINUTE, 'Tue Nov 8 06:14:57 GMT 2016') as 'Minute'
SELECT DATEPART(Day, 'Tue Nov 8 06:14:57 GMT 2016') as 'Day'

and so on.

Thanks in advance, Ziad

Answer 1

This is a way to get a date from your strange string

DECLARE @table TABLE (YourStrangeDate VARCHAR(100));
INSERT INTO @table VALUES('Tue Nov 8 06:14:57 GMT 2016');

WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(YourStrangeDate,' ','</x><x>') + '</x>' AS XML) AS Casted
    FROM @table 
)
SELECT
CAST(
             Casted.value('/x[3]','nvarchar(2)') --third part = "8" 
     + ' ' + Casted.value('/x[2]','nvarchar(3)') --second part = "Nov"
     + ' ' + Casted.value('/x[6]','nvarchar(4)') --6th part = "2016"
     + ' ' + Casted.value('/x[4]','nvarchar(8)') --4th part = Time
     AS DATETIME)
FROM Splitted;

Attention The implicit CAST() to DATETIME will rely on your system's settings. This will fail, if your month's abbreviation is not the one your system expects (e.g. "Dec" is "Dez" on a german system).

From your comment I take, that this is a one time action (CSV import). You might specify SET LANGUAGE English; into the first line to be sure...

With a real date you can continue using the related functions such as DATEPART()

Hint

Always use the appropriate type to store your data! You should use this to repair your data, not for any muddy output...