'Convert varchar column to date in SQL

I have a data table with submitted date column defined as varchar.

The values in the column look like this: "2021-12-02 00:00:00". I want to convert this column to date, how can I do that?

I am using the below query

Select Cast(submitted_date as date) as [submit_date] From schemaname.tablename

I am getting the below error

Invalid operation: “Error converting text to date”

sqldateamazon-redshiftvarcharflutterdart


Solution 1:[1]

There can be multiple issues with a date that can cause this.

One way to possibly check if the data is in the format you're looking for is by using SIMILAR TO and either filter it out or debug further

SELECT DT
     , CASE
           WHEN DT SIMILAR TO '\\d{1,4}-\\d{1,2}-\\d{1,2}\\s\\d{1,2}:\\d{1,2}:\\d{1,2}' THEN 1
           ELSE NULL
    END AS Flag
FROM (SELECT '2000-08-21 00:00:00' AS DT UNION SELECT '2001-08-21 00:00:00' AS DT UNION SELECT '2001-08-AB' AS DT) x
DT Flag
2000-08-21 00:00:00 1
2001-08-21 00:00:00 1
2001-08-AB NULL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Pirate X

Related Questions