T SQL on convert string to DATE

Villa 166 Reputation points
2023-05-03T11:59:08.46+00:00

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 comparisonimage

Select *

	FROM CTE_InDPacket

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

Any help is appreciated.

Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,706 questions
0 comments
Accepted answer
  1. Yitzhak Khabinsky 24,946 Reputation points
    2023-05-03T12:23:52.9466667+00:00

    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;

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,741 Reputation points
    2023-05-03T12:26:21.9366667+00:00

    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
    0 comments