Recommendation for SQL Query if date column data type is NVARCHAR (String)

Problem: If date column in SQL Server table is of NVARCHAR (String) type.

1) Because of nvarchar datatype, date in the column looks like ‘0000-00-00’ or some other date related string.

2) As it is NVARCHAR datatype, we need to use string functions like LEFT,RIGHT or replace to get required value like year / month which will degrade the performance of the query

3) Sorting of data will take more time if date values are as NVARCHAR compare to datetime which reduce the performance the query.

Recommendation:

1) Data type for [date] should be to change to DATETIME

2) If any future dates available that can be converted to ‘1900-01-01’

3) ETL/ETL script should convert any invalid dates to ‘1900-01-01’

4) In this way, we can improve the performance of the query