How to validate the date in a varchar column in SQL Server

Question

I have a staging table which contains all varchar columns. I want to validate a date stored in the data column. Since my staging table contains all varchar columns, then all csv records are inserted into table.

After inserted into the staging table, I need a validation for specific date column to validate date are properly present or not. If any string value comes then I need to eliminate from staging table

Answer 1

Building on @Larnu's comment, you can use TRY_CONVERT to select only the records that contain proper dates, then use those records to do some further action. Consider the following example:

-- Using a table variable as an example of the source data
DECLARE @SampleTable TABLE
(
    Id int,
    SomePossibleDateField varchar(20)
)

-- Now insert some sample data into the table variable, just for illustration 
INSERT INTO @SampleTable
VALUES (1, '2021-05-04'),
   (2, '2021-05-05'),
   (3, 'not a date'),
   (4, NULL),
   (5, ''),
   (6, '2021-05-06')

-- Now select all the records that contain proper dates:
SELECT * FROM @SampleTable WHERE TRY_CONVERT(DATE, [SomePossibleDateField], 120) > '1900-01-01'

The results of the final select statement above are

Id  SomePossibleDateField
1   2021-05-04
2   2021-05-05
6   2021-05-06

Some things to note:

First, in this sample, for simplicity, all the dates are expressed as format 120 (ODBC Canonical). So you may need to try different formats depending on your data. See the date formats listed on the CAST page for the different format values.

Second, that select statement tests for dates greater than the year 1900, but you can change that to any other date that makes sense for your data.

Finally, in case you are looking specifically for records that only contain bad data, you can do that by changing the select statement to something like:

SELECT * FROM @SampleTable 
    WHERE TRY_CONVERT(DATE, [SomePossibleDateField], 120) = ''
    OR TRY_CONVERT(DATE, [SomePossibleDateField], 120) IS NULL

Which results with:

Id  SomePossibleDateField
3   not a date
4   NULL
5   

Unfortunately, an empty string does not result in NULL like bad data does, it simply gets passed through as empty string. So, if you are specifically looking for bad records, you will need to check both for IS NULL and for '' as shown in the example above.