T SQL on convert string to DATE

Question

I am perplexed as to why I can't convert a STRING to date.

I have a string of

12/1/2020

3/26/2019

7/27/2021

11/27/2018

I want to convert these strings to date so I can do the DATE comparison

Select *

	FROM CTE_InDPacket

	WHERE CAST(PacketDate AS DATE) > '3/1/23'

Any help is appreciated.

Thank you

Answer 1

Hi @Villa,

Please try the following.

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, PacketDate  VARCHAR(10));
INSERT @tbl (PacketDate) VALUES
('12/1/2020'),
('3/26/2019'),
('7/27/2021'),
('11/27/2018');
-- DDL and sample data population, end

SELECT * 
	, dt = TRY_CONVERT(DATE, PacketDate, 101)
FROM @tbl;

Answer 2

I can't convert a STRING to date.

"Can't convert" means what in detail; an error message?

Date conversion highly depends on your regional setting, which I don't. An other option is to using CONVERT function with a style parameter, here 101 for dd/mm/yyyy:

;with testData as
    (select '12/1/2020' as DateVal
	 union all select '3/26/2019'
	 union all select '7/27/2021'
	 union all select '11/27/2018')

select *, convert(date, DateVal, 101) as RealDate
from testData