question

RajD-9527 avatar image
0 Votes"
RajD-9527 asked MelissaMa-msft edited

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

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


sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

Why are you mushrooming your question all over different forums?

format-text-field-to-date-time-format


0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @RajD-9527,

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 |
 +-----------------------------------------------------------+------------+

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered
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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @RajD-9527,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.