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