Hi @Raj D ,
Here is how to convert text as a DATE data type.
After that you can format it at will by using CONVERT()
or FORMAT()
functions.
SQL
-- DDL and sample data population, start
DECLARE @processdata TABLE ([ProcessDate] NVARCHAR(255) NOT NULL);
INSERT @processdata VALUES
('Sat May 30 2020 14:19:55 GMT-0700 (Pacific Daylight Time)'),
('Sat May 30 2020 14:19:55');
-- DDL and sample data population, end
DECLARE @separator CHAR(1) = SPACE(1);
SELECT *
, TRY_CAST('<root><r>' +
REPLACE([ProcessDate], @separator, '</r><r>') +
'</r></root>' AS XML)
.value('concat((/root/r[4]/text())[1],"-", (/root/r[2]/text())[1],"-", (/root/r[3]/text())[1])', 'DATE') AS Result
FROM @processdata;
;WITH rs AS
(
SELECT *
, TRY_CAST('<root><r>' +
REPLACE([ProcessDate], @separator, '</r><r>') +
'</r></root>' AS XML)
.value('concat((/root/r[4]/text())[1],"-", (/root/r[2]/text())[1],"-", (/root/r[3]/text())[1])', 'DATE') AS Result
FROM @processdata
)
SELECT *
, TRY_CONVERT(VARCHAR(10), rs.result, 101) AS [Converted]
, FORMAT(rs.result, 'MM/dd/yyyy') AS [Formatted]
FROM rs;
Output
+-----------------------------------------------------------+------------+
| ProcessDate | Result |
+-----------------------------------------------------------+------------+
| Sat May 30 2020 14:19:55 GMT-0700 (Pacific Daylight Time) | 2020-05-30 |
| Sat May 30 2020 14:19:55 | 2020-05-30 |
+-----------------------------------------------------------+------------+