Convert text format to mm/dd/yyyy in sql server

Raj D 581 Reputation points
2021-04-19T20:25:28.037+00:00

Hi, We get some data in flat files and need to convert date in text format to "mm/dd/yyyy". For instance "Sat May 30 2020 14:19:55 GMT-0700 (Pacific Daylight Time)" is formatted as "05/30/2020".

SQL Server version 2016. 

DECLARE @processdata TABLE (
  [ProcessDate] nvarchar(255) NOT NULL);

INSERT @processdata
SELECT 'Sat May 30 2020 14:19:55 GMT-0700 (Pacific Daylight Time)';

Resultset: 

05/30/2020

Thank you

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
Accepted answer
  1. Yitzhak Khabinsky 24,946 Reputation points
    2021-04-19T21:41:47.483+00:00

    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 |  
+-----------------------------------------------------------+------------+
    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-04-19T21:38:56.777+00:00 
    SELECT try_convert(date, substring(ProcessDate, 5, 11))
FROM  @processdata

    I am here assuming that month names are always three letters and dates are always two digits. To keep it simple, I'm ignoring the time part.

    You should always store date and time values in proper data types; you should never store them as strings. Never!

    1 person found this answer helpful.
    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2021-04-22T01:58:02.31+00:00

    Hi @Raj D ,

    Welcome to Microsoft Q&A!

    Glad that you already received your answer.

    Please also refer below: 

     select try_convert(varchar(10), cast(substring([ProcessDate],5,11) as date), 101) from @processdata

    OR 

    select format(cast(substring([ProcessDate],5,11) as date), 'MM/dd/yyyy') from @processdata

    Output:
    05/30/2020

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments