Share via

T SQL on convert string to DATE

Villa 231 Reputation points
May 3, 2023, 11:59 AM

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.
14,205 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 26,201 Reputation points
    May 3, 2023, 12:23 PM

    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
  1. Olaf Helper 45,371 Reputation points
    May 3, 2023, 12:26 PM

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

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.