SQL - Convert varchar to date

Question

SQL - Convert varchar to date

Tim Mullady 221

Hello,

I have table with a date column in varchar data type. Some of the data has leading zero's and other do not. I've been struggling to adjust the data with CAST or Convert.

example:
07/13/2020
7/13/2020

I wanted to see if someone could help me adjust it to date type M/DD/YYYY

Thanks,

Tim

Viorel 106.5K Reputation points

2021-02-11T18:29:40.513+00:00
Since the column is a text, did you consider string operations instead of cast?

update [Table] set [Column] = right([Column], len([Column])-1) where [Column] like '0%'
Guoxiong 8,126 Reputation points

2021-02-11T18:36:33.98+00:00

How about the day part (DD)? Do you want 7/01/2020 or 7/1/2020?
Tim Mullady 221 Reputation points

2021-02-11T18:44:13.97+00:00

I'm thinking I need to convert everything to MM/DD/YYYY. So all months and days would have leading zero's. Then pass text function into a Convert or CAST. Adding leading zeros to both day and month would make the text function work

.
Tim Mullady 221 Reputation points

2021-02-11T18:47:33.203+00:00

convert(date, substring([date],5,4) + substring([date],1,2) + substring([]date,3,2),103)
Yitzhak Khabinsky 24,016 Reputation points

2021-02-11T19:10:26.003+00:00

What is your SQL Server version?

Viorel 106.5K Reputation points

2021-02-11T18:29:40.513+00:00

Since the column is a text, did you consider string operations instead of cast?

update [Table] set [Column] = right([Column], len([Column])-1) where [Column] like '0%'
Guoxiong 8,126 Reputation points

2021-02-11T18:36:33.98+00:00

How about the day part (DD)? Do you want 7/01/2020 or 7/1/2020?
Tim Mullady 221 Reputation points

2021-02-11T18:44:13.97+00:00

I'm thinking I need to convert everything to MM/DD/YYYY. So all months and days would have leading zero's. Then pass text function into a Convert or CAST. Adding leading zeros to both day and month would make the text function work

.
Tim Mullady 221 Reputation points

2021-02-11T18:47:33.203+00:00

convert(date, substring([date],5,4) + substring([date],1,2) + substring([]date,3,2),103)
Yitzhak Khabinsky 24,016 Reputation points

2021-02-11T19:10:26.003+00:00

What is your SQL Server version?

Answer 1

Nasreen Akter 10,706

Hi @Tim Mullady ,

would you please try

FORMAT(CONVERT(datetime, ColumnName, 101), 'MM/dd/yyyy')

Thanks!

Tim Mullady 221 Reputation points

2021-02-11T19:13:22.383+00:00

worked thx

Answer 2

DECLARE @t TABLE (
 [Date] varchar(20)
);
INSERT INTO @t VALUES
('07/13/2020'), ('7/13/2020'), ('7/01/2020');

-- Output format 7/1/2020
SELECT REPLACE(CASE WHEN LEFT([Date], 1) = '0' THEN SUBSTRING([Date], 2, LEN([Date]) - 1) ELSE [Date] END, '/0', '/')
FROM @t;

--Output format 07/01/2020
SELECT CONVERT(varchar(10), CAST([Date] AS date), 101)
FROM @t;

Answer 3

Tom Phillips 17,691

If you are using SQL 2016+

DECLARE @t TABLE (
     [Date] varchar(20)
 );
 INSERT INTO @t VALUES
 ('07/13/2020'), ('7/13/2020'), ('7/01/2020');


 SELECT FORMAT(TRY_CAST([Date] as date),'M/d/yyyy')
 FROM @t

SQL - Convert varchar to date

2 additional answers

Activity