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 118.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,206 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 26,201 Reputation points

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

What is your SQL Server version?

Accepted answer

Nasreen Akter 10,806 Reputation points

2021-02-11T19:07:50.617+00:00
Hi @Tim Mullady ,

would you please try

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

Thanks!
Please sign in to rate this answer.

1 person found this answer helpful.
Tim Mullady 221 Reputation points

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

worked thx
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

Guoxiong 8,206

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;

Tom Phillips 17,741 Reputation points

2021-02-11T19:00:45.7+00:00
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
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL - Convert varchar to date

2 additional answers

Your answer