Best and most efficient way to convert a VARCHAR(10) to a Date for filtering

Use an unambiguous, set-based conversion and avoid changing server/session settings. Assuming the VARCHAR(10) column always contains a valid, consistent date format, the pattern is:

-- Example: column stores dates as 'YYYYMMDD'
SELECT  ...
FROM    dbo.YourBlobTable AS b
WHERE   CONVERT(date, b.YourVarcharDateColumn, 112) >= DATEADD(YEAR, -3, CAST(GETDATE() AS date));

Key points:

1. Use an unambiguous style code
   • If the data is YYYYMMDD, use style 112 (ISO, no delimiters) as above.
   • If the data is YYYY-MM-DD, date/datetime2 will interpret it correctly without a style for modern types, but using a style or normalizing to YYYYMMDD is safer and clearer.
   • Avoid ambiguous formats like MM/DD/YYYY or DD/MM/YYYY for storage and conversion.
2. Filter on the converted date
   • Convert the VARCHAR to date (or datetime2) in the predicate and compare to a date boundary:

WHERE CONVERT(date, b.YourVarcharDateColumn, 112)
      >= DATEADD(YEAR, -3, CAST(GETDATE() AS date));

3. For performance and indexing
   • If this table is large and the column is indexed as VARCHAR, converting in the WHERE clause prevents index seek usage.
   • For best performance, add a persisted computed column of type date and index that:

ALTER TABLE dbo.YourBlobTable
ADD YourDateColumn AS CONVERT(date, YourVarcharDateColumn, 112) PERSISTED;

CREATE INDEX IX_YourBlobTable_YourDateColumn
ON dbo.YourBlobTable(YourDateColumn);

SELECT ...
FROM   dbo.YourBlobTable
WHERE  YourDateColumn >= DATEADD(YEAR, -3, CAST(GETDATE() AS date));

4. Long-term recommendation
   • Store dates in a proper date/datetime data type going forward (e.g., date or datetime2) and only format as text in the presentation layer. This avoids reliance on DATEFORMAT or language settings and uses the unambiguous YYYYMMDD/YYYY-MM-DD formats internally.

References:

• Data Type Conversions
• Data Conversion Using the ODBC Driver for DB2
• Integration Services Data Types
• CHANGING DATE FORMAT OF SQL SERVER DATABASE - Microsoft Q&A
• Using IsNull with different data types in SQL SERVER - Microsoft Q&A
• Sorting MS Access Dates Desc - Microsoft Q&A